SAS List of variables containing special characters

0
votes

I am a novice for SAS programming and I am trying to create a list of old_variable names so I can work with them (subset, rename, spaces, etc.), however the variable names have funky characters (#, parenthesis, single quotes, numbers, you name it). Each variable is delimited-separated by ';' and the source file is in csv format. I need to do it for 44 different files and each file has about 199 variables.

So far, I have tried a macro where I create a list of the variables, however, the code fails when I try to use the macro &vars because of the special characters. I have checked SAS paper 005-2013, however I believe I am not really sure how to use the functions in my code.

Any insights or directions would be appreciated. Here is the code I tried so far:

1) Importing:

proc import datafile='file_oldname.csv'
 dbms=csv
 out= oldName
 replace;
 delimiter=',';
 getnames=yes;  
run;

2) Making my list of oldNames; * A macro variable contanining the oldvariables names; * Using Proc Contents and Proc SQL;

proc contents data=oldName out=listOldName;
run;

options VALIDMEMNAME=EXTEND;
proc sql noprint;
select distinct(name) into:vars separated by " " from listOldName;
quit;

%put &vars;

&vars contains the list of variables, however if I try to use it, it fails because of the special characters.

How can I wrap the &vars properly so that the variable names with special characters can be used? I want to further renamed them by new names.

Thanks a lot!

2
sasspecial-characters
This may help: stackoverflow.com/questions/9041632/sas-safe-column-names – Robert Penridge

2 Answers

0
votes

As your variable names contain special characters, they need to be referenced in "name literal!"n format.

Try:

options VALIDVARNAME=ANY;
proc sql noprint;
select distinct nliteral(name) 
  into:vars separated by ' ' 
  from listOldName;

%put &=var;

More info on name literals in documentation.

Edit - as kindly pointed out by @Tom, the nliteral function handily covers the conversion you require! Documentation for that is here.

0
votes

When Proc IMPORT runs it creates data sets with variable names that meet the VALIDVARNAME setting at import time. Thus, one alternative, would be to set the option prior to IMPORT to ensure 'clean' variable names, and reset it afterwards.

%let setting = %sysfunc(getoption(VALIDVARNAME));

options validvarname=v7;
Proc IMPORT ... ;
  ...;
run;

options validvarname = &SETTING;

Example

filename have temp;

data _null_;
  file have;
  put 'Name,"Age","Date of Birth",School:First,Surprise!,-Feedback';
  put 'Abel,0,Yesterday,Eastern Acacdemy,Balloons!,None';
run;

options validvarname=any;
proc import file=have replace out=have_any dbms=csv;
  getnames=yes;
run;

options validvarname=v7;
proc import file=have replace out=have_v7 dbms=csv;
  getnames=yes;
run;

options validvarname=any;