topic Re: Rename variables with the same string suffix in different data sets to the same name in New SAS User

Rename variables with the same string suffix in different data sets to the same name

windy — Tue, 29 Oct 2019 06:06:34 GMT

Hi all,

My problem is I have about 400 data sets related to different cities. In each data set, there are a variable for unemployment rate and a variable for date ranging over the same period. I want to stack all 400 unemployment rate series into one panel data set (city and time). To be able to do that, the unemployment rate series need to have the same name. Right now, the variable name for UER in each small data set is like '....URN'. There's no underscore in the name. The front part contains different letters and sometime numbers.

Can someone please help me to solve this problem? I really appreciate.

Thanks in advance,

Windy.

Re: Rename variables with the same string suffix in different data sets to the same name

PeterClemmensen — Tue, 29 Oct 2019 06:15:23 GMT

@windy Hi and welcome to the SAS Community 🙂

Are the variables of interest all in the same position in each data set?

Re: Rename variables with the same string suffix in different data sets to the same name

windy — Tue, 29 Oct 2019 06:17:32 GMT

@PeterClemmensen Yeah, they are the fourth variable in every data set.

Re: Rename variables with the same string suffix in different data sets to the same name

PeterClemmensen — Tue, 29 Oct 2019 07:15:48 GMT

Are all of the other variables equal named in the data sets?

Re: Rename variables with the same string suffix in different data sets to the same name

windy — Tue, 29 Oct 2019 07:43:51 GMT

Yeah. The other variables are the same in all data sets.

Re: Rename variables with the same string suffix in different data sets to the same name

PeterClemmensen — Tue, 29 Oct 2019 08:24:42 GMT

No need to rename them in the original data sets. This will create the data you want and dynamically rename the variables in the Set Statement.

Simply rename the 'WORK' to your actual library..

data one;   var1=1; var2=2; var3=3;   oneURN=4; run;
data two;   var1=5; var2=6; var3=7;   twoURN=8; run;
data three; var1=9; var2=1; var3=2; threeURN=3; run;

data _null_;
   set sashelp.vcolumn end=lr;
   where libname='WORK' and substr(name, length(name)-2, 3)='URN';
 
   if _n_ = 1 then call execute('data want; set ');
 
   call execute(cat(memname, '(rename=', name, '=NewVar)'));
 
   if lr then call execute(';run;');
run;

Result:

var1 var2 var3 NewVar
1    2    3    4
9    1    2    3
5    6    7    8

Re: Rename variables with the same string suffix in different data sets to the same name

windy — Wed, 30 Oct 2019 00:40:43 GMT

It's not working yet.

Here is the note I received. I already changed the libname to my actual library, but the same note still appears.

NOTE: There were 0 observations read from the data set SASHELP.VCOLUMN.
WHERE (libname='WORK') and (SUBSTR(name, (LENGTH(name)-2), 3)='URN');

Re: Rename variables with the same string suffix in different data sets to the same name

Quentin — Wed, 30 Oct 2019 01:14:16 GMT

Are the 400 data sets in the WORK library? If not, you'll have to change the libname on the WORK statement from 'WORK' to whatever your libname is.

Try playing around with:

proc print data=sashelp.vcolumn ;
   where libname='WORK' and substr(name, length(name)-2, 3)='URN';
run;

That should list all of the variables for data sets in the WORK library where variable name ends with 'URN' (sounds like currently it won't print anything). Try changing the WHERE statement until it finds the variables you want. Then you can use that WHERE statement in the DATA _NULL_ step.

Re: Rename variables with the same string suffix in different data sets to the same name

windy — Wed, 30 Oct 2019 02:01:18 GMT

Oh yeah. It's working now.

But I need to move all of data sets to the work library because this code

if _n_ = 1 then call execute('data want; set ');

execute the data sets in the work library. I was trying to change to the actual library, but the code didn't work.

Anyway, thank you so much for your help @PeterClemmensen and @Quentin. Really appreciate that.