So are the variables literally named XYZ.....YYYY ?
Or does the XYZ also change?
I find it much easier to work with data in datasets than in MACRO code. So get the list of variable names into a dataset, say with PROC CONTENTS. Then apply logic to generate the NEWNAME from the original NAME. Now you have the information you might want to use to rename the variables.
So let's say the problem is to combine data from a series of dataset with names like TABLE...yyyy where yyyy is a four digit year. And each dataset has exactly variables with a names like ...AAAyyyy where yyyy is the same four digit year that is in the dataset name and AAA is the 3 letters to be removed. So a query like this should get the list of variable names that need to be renamed and the target new name.
proc sql;
create table renames as
select libname
, memname
, substrn(memname,length(memname)-3) as year
, name
, cats(substrn(name,1,length(name)-7),calculated year) as newname
from dictionary.columns
where libname = 'WORK'
and memname like 'TABLE%'
and length(name) > 7
and substrn(memname,length(memname)-3) = substrn(name,length(name)-3)
and 1980 <= input(substrn(name,length(name)-3),?4.) <= year(today())
;
quit;
Let's try if for some of your original datasets:
data table_1985;
input id XYZ81I1985 WXYZ81I1985;
datalines;
1 2 2
2 3 3
3 5 5
;
data table_1986;
input id XYZ86I1986 WXYZ86I1986;
datalines;
1 4 5
5 10 10
6 17 17
;
data table_1987;
input id XYZ86I1987 WXYZ86I1987;
datalines;
1 6 7
8 20 19
9 30 30
;
Result
Obs libname memname year name newname
1 WORK TABLE_1985 1985 XYZ81I1985 XYZ1985
2 WORK TABLE_1985 1985 WXYZ81I1985 WXYZ1985
3 WORK TABLE_1986 1986 XYZ86I1986 XYZ1986
4 WORK TABLE_1986 1986 WXYZ86I1986 WXYZ1986
5 WORK TABLE_1987 1987 XYZ86I1987 XYZ1987
6 WORK TABLE_1987 1987 WXYZ86I1987 WXYZ1987
Now that we have that data we can use it to write some code.
Perhaps you want code to MERGE the datasets?
So you could use code like this
filename code temp;
data _null_;
set renames end=eof;
by memname;
file code;
if _n_=1 then put 'merge';
if first.memname then put libname +(-1) '.' memname '(rename=(' @;
put name '=' newname @;
if last.memname then put '))';
if eof then put ';';
run;
To create the MERGE statement. Which we could then use in a step like this
data want;
%include code/source2;
by id ;
run;
to combine the datasets to get:
Obs id XYZ1985 WXYZ1985 XYZ1986 WXYZ1986 XYZ1987 WXYZ1987
1 1 2 2 2 2 2 2
2 2 3 3 . . . .
3 3 5 5 . . . .
4 5 . . 10 10 . .
5 6 . . 17 17 . .
6 8 . . . . 20 19
7 9 . . . . 30 30
But it will probably be more usable to SET the datasets and put the YEAR into its own variable.
proc sql;
create table renames as
select libname
, memname
, catx('.',libname,nliteral(memname)) as dsname
, substrn(memname,length(memname)-3) as year
, nliteral(name) as name
, nliteral(substrn(name,1,length(name)-7)) as newname
from dictionary.columns
where libname = 'WORK'
and memname like 'TABLE%'
and length(name) > 7
and substrn(memname,length(memname)-3) = substrn(name,length(name)-3)
and 1980 <= input(substrn(name,length(name)-3),?4.) <= year(today())
;
quit;
filename code temp;
data _null_;
set renames end=eof;
by memname;
file code;
if first.memname then put dsname '(rename=(' @;
put name '=' newname @;
if last.memname then put '))';
run;
data want;
length id 8 year 8;
set
%include code/source2;
indsname=dsname
;
by id ;
year = input(substrn(dsname,length(dsname)-3),4.);
run;
Result
Obs id year XYZ WXYZ
1 1 1985 2 2
2 1 1986 2 2
3 1 1987 2 2
4 2 1985 3 3
5 3 1985 5 5
6 5 1986 10 10
7 6 1986 17 17
8 8 1987 20 19
9 9 1987 30 30
Click image to register for webinar
