BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
nketata
Obsidian | Level 7

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.

 

1 ACCEPTED SOLUTION

Accepted Solutions
gamotte
Rhodochrosite | Level 12

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;

View solution in original post

10 REPLIES 10
gamotte
Rhodochrosite | Level 12

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;
nketata
Obsidian | Level 7

For some reasons the FROM dictionary.columns does not work;

the macro variable  :renames is not created and cannot be called later.

gamotte
Rhodochrosite | Level 12
What do you mean by "does not work". Which message in displayed in the log ?
nketata
Obsidian | Level 7

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

 

nehalsanghvi
Pyrite | Level 9

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.

nketata
Obsidian | Level 7

My bad . It works now.

nehalsanghvi
Pyrite | Level 9

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;
Astounding
PROC Star

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

nketata
Obsidian | Level 7

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.

 

 

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 Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 16098 views
  • 6 likes
  • 5 in conversation