DATA Step, Macro, Functions and more

HOW TO REMOVE THE 3 FIRST LETTERS IN ALL THE VARIABLES NAMES

Accepted Solution Solved
Reply
Contributor
Posts: 34
Accepted Solution

HOW TO REMOVE THE 3 FIRST LETTERS IN ALL THE VARIABLES NAMES

[ Edited ]

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.

 


Accepted Solutions
Solution
‎02-28-2017 12:30 PM
Regular Contributor
Posts: 194

Re: HOW TO REMOVE THE 3 FIRST LETTERS IN ALL THE VARIABLES NAMES

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


All Replies
Solution
‎02-28-2017 12:30 PM
Regular Contributor
Posts: 194

Re: HOW TO REMOVE THE 3 FIRST LETTERS IN ALL THE VARIABLES NAMES

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;
Contributor
Posts: 34

Re: HOW TO REMOVE THE 3 FIRST LETTERS IN ALL THE VARIABLES NAMES

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

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

Regular Contributor
Posts: 194

Re: HOW TO REMOVE THE 3 FIRST LETTERS IN ALL THE VARIABLES NAMES

What do you mean by "does not work". Which message in displayed in the log ?
Contributor
Posts: 34

Re: HOW TO REMOVE THE 3 FIRST LETTERS IN ALL THE VARIABLES NAMES

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

 

Frequent Contributor
Posts: 75

Re: HOW TO REMOVE THE 3 FIRST LETTERS IN ALL THE VARIABLES NAMES

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.

Contributor
Posts: 34

Re: HOW TO REMOVE THE 3 FIRST LETTERS IN ALL THE VARIABLES NAMES

My bad . It works now.

Frequent Contributor
Posts: 75

Re: HOW TO REMOVE THE 3 FIRST LETTERS IN ALL THE VARIABLES NAMES

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;
Super User
Posts: 5,094

Re: HOW TO REMOVE THE 3 FIRST LETTERS IN ALL THE VARIABLES NAMES

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.

Super User
Super User
Posts: 7,420

Re: HOW TO REMOVE THE 3 FIRST LETTERS IN ALL THE VARIABLES NAMES

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.

Contributor
Posts: 34

Re: HOW TO REMOVE THE 3 FIRST LETTERS IN ALL THE VARIABLES NAMES

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.

 

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 221 views
  • 2 likes
  • 5 in conversation