topic How to rename a variable using filename in macro in SAS Data Management

How to rename a variable using filename in macro

tritringuyen — Mon, 10 Jul 2017 09:20:46 GMT

Hello friends,

I want to rename a variable in macro by using filename (variable name = filename). I use the following code to import files and then use macro:

/* Read datafiles in folder*/

%let subdir=C:\Users\Tri_Tri_nguyen\Desktop\Dead_final\;
filename dir "&subdir.*.csv ";
data new;
length filename fname $ 256;
infile dir eof=last filename=fname;
input ;
last: filename=fname;
run;

/* sort data and remove duplicates-*/

proc sort data=new nodupkey;
by filename;
run;

data null;
set new;
call symputx(cats('filename',_n_),filename);
call symputx(cats('dsn',_n_),compress(scan(filename,-2,'\.') ,'ka'));
call symputx('nobs',_n_);
run;

%put &nobs.;

%macro import;
%do i=1 %to &nobs;

/* Import for file No. 9 of board of directors */

proc import
datafile="&&filename&i"
out=dsn&i
dbms=csv
replace;
getnames=no;
run;

run;

....

data Dsn&i; set Dsn&i;
rename COL1=WC&filename; /* I want to rename COL1 (in the imported file) using words "WC" and the name of the files I have imported above". For example, the imported file name is 12345, I want to rename COL1 into "wc12345". I got a lot of files like this (that is why I need a macro) */
run;

...

...

%end;
%mend import;
%import

Everything in the macro works well, except rename issue. Thank you very much for your support!

Cheers, Thierry.

Re: How to rename a variable using filename in macro

Kurt_Bremser — Mon, 10 Jul 2017 09:27:30 GMT

You are using &filename; why are you not using &&filename&i?

If that is not the cause of your problem, post the log, and how the result differs from your expectations.

Re: How to rename a variable using filename in macro

tritringuyen — Mon, 10 Jul 2017 09:50:40 GMT

Thank you very much for your response. The log is follows:

MPRINT(IMPORT): * Step 4: Rename variables;
SYMBOLGEN: Macro variable I resolves to 2
MPRINT(IMPORT): data Dsn22;
SYMBOLGEN: Macro variable I resolves to 2
MPRINT(IMPORT): set Dsn22;
SYMBOLGEN: && resolves to &.
SYMBOLGEN: && resolves to &.
SYMBOLGEN: Macro variable I resolves to 2
SYMBOLGEN: Macro variable FILENAME2 resolves to
C:\Users\Tri_Tri_nguyen\Desktop\Dead_final\01100.csv
NOTE: Line generated by the macro variable "FILENAME2".
1763 WCC:\Users\Tri_Tri_nguyen\Desktop\Dead_final\01100.csv
-
22
76
MPRINT(IMPORT): rename
COL1=WCC:\Users\Tri_Tri_nguyen\Desktop\Dead_final\01100.csv;
MPRINT(IMPORT): run;

ERROR 22-322: Syntax error, expecting one of the following: a name, ;.

ERROR 76-322: Syntax error, statement will be ignored.

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.DSN22 may be incomplete. When this step was
stopped there were 0 observations and 7 variables.
WARNING: Data set WORK.DSN22 was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds

Basically, the variables are not renamed in the macro. I want to merge datasets after import and do some modifications. It is essential to rename a variable using filename. Please have a look at them and give me some advice. Thank you very much!

Cheers, Thierry

Re: How to rename a variable using filename in macro

tritringuyen — Mon, 10 Jul 2017 09:53:14 GMT

Also, the name of datasets in my actual SAS code is Dsn2&i (not Dsn&i as stated in the question above).

Re: How to rename a variable using filename in macro

Kurt_Bremser — Mon, 10 Jul 2017 09:58:02 GMT

C:\Users\Tri_Tri_nguyen\Desktop\Dead_final\01100.csv

is NOT a valid SAS name.

SAS names consist of the 26 letters of the standard alphabet, digits, or underlines, and must not start with a digit.

I suggest you build a valid SAS name from the name of your infile, like

file_01100

where 01100 is the part of the filename between the final backslash and the dot.

Re: How to rename a variable using filename in macro

tritringuyen — Mon, 10 Jul 2017 10:08:32 GMT

Yes, that is exactly what I want. I only want a variable name 'wc01100', where 01100 is the last part of file name. If so, how I should modify sas code? Again, it is in a macro.

Thanks, Thierry

Re: How to rename a variable using filename in macro

Kurt_Bremser — Mon, 10 Jul 2017 11:38:29 GMT

Extract the important part with scan() functions:

%let filename2=C:\Users\Tri_Tri_nguyen\Desktop\Dead_final\01100.csv;

data _null_;
whole_path = "&filename2";
col1_name = 'wc' !! scan(scan(whole_path,-1,'\'),1,'.');
call symput('col1_name',trim(col1_name));
run;

%put &col1_name.;

Now you can use &col1_name. in the rename statement.

Re: How to rename a variable using filename in macro

tritringuyen — Mon, 17 Jul 2017 15:26:37 GMT

Thank you very much! It does not work. However, I may try rename manually. Thank you very much!