topic Re: HOW TO REMOVE THE 3 FIRST LETTERS IN ALL THE VARIABLES NAMES in SAS Programming

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

nketata — Tue, 28 Feb 2017 16:10:44 GMT

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.

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

gamotte — Tue, 28 Feb 2017 16:20:03 GMT

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;

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

nehalsanghvi — Tue, 28 Feb 2017 16:22:38 GMT

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;

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

Astounding — Tue, 28 Feb 2017 16:34:21 GMT

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.

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

RW9 — Tue, 28 Feb 2017 16:36:15 GMT

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.

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

nketata — Tue, 28 Feb 2017 16:38:35 GMT

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

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

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

gamotte — Tue, 28 Feb 2017 16:47:17 GMT

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

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

nketata — Tue, 28 Feb 2017 16:55:23 GMT

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.

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

nketata — Tue, 28 Feb 2017 17:19:17 GMT

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;

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

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

nehalsanghvi — Tue, 28 Feb 2017 17:23:01 GMT

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.

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

nketata — Tue, 28 Feb 2017 17:30:06 GMT

My bad . It works now.