I tried the solution you suggest before posting my question on this forum. it does not work. If you change the variable name in the first step , then when you go to call the actual data from the external library the variables which are renamed cannot be found.
This is my code:
proc sql;
create table DATLST as
select memname,name
from dictionary.columns
where upcase(libname)="EXT" and (upcase(name)in("ID") or upcase(name) like ("%DAT") );
quit;
Proc sort data= datlst ; by memname name ; run;
%macro loop();
%local TOTCNT rpt dsin keepvar;
proc sql noprint;
select count(memname)
into :TOTCNT
from DATLST;
quit;
%put datasets= &TOTCNT;
%let rpt=1;
%do %while (&rpt.<= 2/*&TOTCNT.*/);
data _null_;
set DATLST (firstobs=&rpt. obs=&rpt.);
call symput("dsin",strip(memname));
run;
data ds0;
set DATLST;
where memname="&dsin.";
run;
proc transpose data=ds0 out=tr_ds0(drop=_name_ _label_);
by memname ;
var name;
run;
proc sql noprint ;
select count(distinct name )into :varnum
from ds0 ;
%put varnum = &varnum;
data tr_ds ;
length keepvar_ $600;
set tr_ds0 ;
retain keepvar_;
%do i=1 %to &varnum.;
keepvar_=catx(" " ,keepvar_,col&i.);
call symput("keepvar",keepvar_);
i+1;
%end;
%put vars to keep= &keepvar;
run;
data ds1;
length fromdat $200;
set idraw.&dsin.;
fromdat="&dsin.";
keep fromdat &keepvar.;
run;
/*At this stage my ds1 dataset has 4 variables: ID, FROMDAT, XXXDAT, XXXDAT. I don't know what the XXX is and I want to do the renaming automatically.SAS does allow to manipulate variable who have the same prefix with the colon e.g SAME:. I am wondering if there is a way to do it with variables with the same suffix. */
data ds2;
set ds1;
/*renaming DAT variables*/
run;
%let rpt=%eval(&rpt.+1);
%put rpt= &rpt.;
%end;
%mend;
%loop()
... View more