Hello,
I have a file where all the variables start with a prefix I want to remove. These varables does not have a suffix and do not have a pattern in their name (like var1 var2 var3 etc.):
So basically, I have:
DATA ALPHA;
UP_HOW=11;
UP_ARE=22;
UP_YOU=33; run;
I want to remove the UP_ in the names.
I have been trying with this macro, but I was unsuccessful:
%macro RenameList(vars= );
%let list=;
*%do i = 1 %to %sysfunc(countw(&vars));
%let var=%substr(&vars,4,50);
%let list= &var;
%end;
&list
%mend;
Thanks for your help.
Hello,
You can generate the renaming affectations as follows :
DATA ALPHA;
UP_HOW=11;
UP_ARE=22;
UP_YOU=33;
run;
proc sql noprint;
SELECT cats(NAME,"=",substr(NAME,4))
INTO :renames SEPARATED BY " "
FROM dictionary.columns
WHERE LIBNAME="WORK" AND MEMNAME="ALPHA";
quit;
data want;
set Alpha;
rename &renames;
run;
Hello,
You can generate the renaming affectations as follows :
DATA ALPHA;
UP_HOW=11;
UP_ARE=22;
UP_YOU=33;
run;
proc sql noprint;
SELECT cats(NAME,"=",substr(NAME,4))
INTO :renames SEPARATED BY " "
FROM dictionary.columns
WHERE LIBNAME="WORK" AND MEMNAME="ALPHA";
quit;
data want;
set Alpha;
rename &renames;
run;
For some reasons the FROM dictionary.columns does not work;
the macro variable :renames is not created and cannot be called later.
Here is a copy of the log:
24318 proc sql noprint;
24319 SELECT cats(NAME,"=",substr(NAME,4))
24320 INTO :renames SEPARATED BY " "
24321 FROM dictionary.columns
24322 WHERE LIBNAME="WORK" AND MEMNAME="realfile";
NOTE: No rows were selected.
24323 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.04 seconds
cpu time 0.01 seconds
24324
24325 data want;
24326 set realfile;
24327 rename &renames;
-
22
200
WARNING: Apparent symbolic reference RENAMES not resolved.
ERROR 22-322: Syntax error, expecting one of the following: a name, ;.
ERROR 200-322: The symbol is not recognized and will be ignored.
24328 run;
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.WANT may be incomplete. When this step was stopped there were 0
observations and 13 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
Make sure the value for Memname is in all caps:
MEMNAME="REALFILE"
All memname values in the dictionary.columns table are capitalized. Same goes for libname.
My bad . It works now.
Here's another way:
DATA ALPHA;
UP_HOW=11;
UP_ARE=22;
UP_YOU=33;
run;
proc sql;
select count(*) into :varcount
from dictionary.columns
where libname='WORK'
and memname='ALPHA';
quit;
proc sql;
select name, substr(name,4) into :currname1-:currname3, :newname1-:newname3
from dictionary.columns
where libname='WORK'
and memname='ALPHA';
quit;
%macro changevarnames;
data ALPHA2;
set ALPHA;
%do j=1 %to &varcount.;
rename &&currname&j.=&&newname&j.;
%end;
run;
%mend;
%changevarnames;
Food for thought: Consider the possibility that you might be better off removing 2 characters instead of 3.
If you have a variable name like UP_3D, removing 3 characters will generate an invalid variable name. Leaving the underscore in place will always create a valid variable name.
The question should be, why do you have that in the first place? Either the data you have is fine and workable hence you have no need to rename, or the data is currently wrong, and you need to change the data before you get to this stage. In most cases something further up the line is to blame, database export, data import not done right, etc. There are many techincal avenues to do this process - you could datalines read in the data again, run a list of renames as given above etc. but that wouldn't exaplain the why of it.
Well,
I work for government so you can imagine my file does not have 3 variables.
Second, I am in a process where the data are pushed to me; I have no control on the input and the rename has to be dynamic because the number of variables to be renamed can change.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.