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

[windy]

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. 

[PeterClemmensen]

@windy Hi and welcome to the SAS Community 🙂

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

[windy]

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

[PeterClemmensen]

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

[windy]

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

[PeterClemmensen · Accepted solution]

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

[windy]

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');

[Quentin]

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. 

[windy]

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.