Dear all, I have a dataset with variables that have very long names. I am trying to write a macro that would rename all the variables by selecting some characters from the original name, but I get an "ERROR 180-322: Statement is not valid or it is used out of proper order" message. Could you please look at the code below and tell me what is wrong?
/*1. Generate a sample dataset with long, random strings as variable names (use the code from 51386 - Creating character variables that contain random alphabetic values*/
data a(keep=final);
length final $30;
do i=1 to 10;
do j=1 to 30;
substr(final,j)=byte(floor(65+26*ranuni(0)));
end;
output;
end;
run;
proc sql noprint;
select final into :rand_names separetd by " " from a;
quit;
data sample;
input (&rand_names) (:best2.);
datalines;
1 2 3 4 5 6 7 8 9 10
;
run;
/* 2. Macro that renames each variable name by selecting some characters from the original name. It's a modified version of the following macro: http://www2.sas.com/proceedings/sugi28/118-28.pdf*/
%macro rename;
proc sql noprint;
select nvar into :num_vars from dictionary.tables where libname="WORK" and memname="SAMPLE"; /*number of variables to rename, last value of the do loop below*/
proc sql noprint;
select distinct(name) into :vars separated by " " from dictionary.columns
where libname="WORK" and memname="SAMPLE" ; /* create macro variable with names separated by space*/
quit;
proc datasets library=WORK noprint; /* Rename variables, using datasets procedure*/
modify SAMPLE;
rename
%do i=1 %to &num_vars;
%let variable=%sysfunc(scan(&vars,&i)); /* a variable to rename*/
%let new_var=%sysfunc(substr(&variable,3,2)); /*new, shorter name - 2 characters from the original name*/
&variable=&new_var;
%end;
;
quit;
run;
%mend rename;
%rename;
The LOG message is as follows
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time 0.01 seconds
NOTE: Writing HTML Body file: sashtml.htm
NOTE: Renaming variable CVDHBIFOFXGLXNRHJMLGQVQNKUWAMZ to DH. <------- it renames the first variable
NOTE: Enter RUN; to continue or QUIT; to end the procedure.
NOTE: Line generated by the macro variable "VARIABLE". <--------- but fails to rename the others
1 DFYSDSYBLCZQTHNGJWCVMFHQCEPSKV
------------------------------
180
NOTE: Enter RUN; to continue or QUIT; to end the procedure.
NOTE: Line generated by the macro variable "VARIABLE".
1 EXCUIOLRFKKSMVJYUVLKOHYANPLNYH
------------------------------
180
NOTE: Enter RUN; to continue or QUIT; to end the procedure.
NOTE: Line generated by the macro variable "VARIABLE".
1 EYVOICPZSPBAVUOEGIUAANCUYFKNDL
------------------------------
180
NOTE: Enter RUN; to continue or QUIT; to end the procedure.
NOTE: Line generated by the macro variable "VARIABLE".
1 FXBUOVXJZHHRMZLTFENRPEFJEDGCUN
------------------------------
180
NOTE: Enter RUN; to continue or QUIT; to end the procedure.
NOTE: Line generated by the macro variable "VARIABLE".
1 JTKKGJWFIZIRARHMIEJEIMEKVPFSMR
------------------------------
180
NOTE: Enter RUN; to continue or QUIT; to end the procedure.
NOTE: Line generated by the macro variable "VARIABLE".
1 NDTKWLEAPVYAWEFROLFGTKRIRELMZF
------------------------------
180
NOTE: Enter RUN; to continue or QUIT; to end the procedure.
NOTE: Line generated by the macro variable "VARIABLE".
1 NFZXCHYDMGMGOLGFKWZVMNXPHXVARZ
------------------------------
180
NOTE: Enter RUN; to continue or QUIT; to end the procedure.
NOTE: Line generated by the macro variable "VARIABLE".
1 QKZJOBBEKLLNCWSEYGAGYLHFCNVMHI
------------------------------
180
NOTE: Enter RUN; to continue or QUIT; to end the procedure.
NOTE: Line generated by the macro variable "VARIABLE".
1 VLGARSNXSBLCIEVUZGQLLUFIHQBDHH
------------------------------
180
ERROR 180-322: Statement is not valid or it is used out of proper order.
NOTE: Statements not processed because of errors noted above.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE DATASETS used (Total process time):
real time 0.81 seconds
cpu time 0.26 seconds
The macro managed to rename the first variable, but failed to do this with the others. What am I doing wrong?
Thanks,
Chris
do not put the semicolon in this line: &variable=&new_var;
Remember SAS macros are doing text string manipulation on SAS code. The ; will end the rename statement while your intention is to do al lot of them
Add a line with just a single semicolon after the macro do-loop. In that way you are getting the text/string of the SAS code correct.
Can be confusing as a semicolon has a distinct role
do not put the semicolon in this line: &variable=&new_var;
Remember SAS macros are doing text string manipulation on SAS code. The ; will end the rename statement while your intention is to do al lot of them
Add a line with just a single semicolon after the macro do-loop. In that way you are getting the text/string of the SAS code correct.
Can be confusing as a semicolon has a distinct role
Hello,
You just have to delete the semicolon from &variable=&new_var;
The rename syntax of proc datasets is:
rename a=x b=y etc.
Thank you, Jaap and Loko. Now it works. Thanks for help
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.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.