BookmarkSubscribeRSS Feed
tritringuyen
Quartz | Level 8

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.

7 REPLIES 7
Kurt_Bremser
Super User

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.

 

tritringuyen
Quartz | Level 8

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

 

tritringuyen
Quartz | Level 8

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

Kurt_Bremser
Super User

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.

 

tritringuyen
Quartz | Level 8
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
Kurt_Bremser
Super User

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.

tritringuyen
Quartz | Level 8

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 7 replies
  • 1937 views
  • 1 like
  • 2 in conversation