Hello,
I would like to automatically select tables from the library and append them all. Before that I need also a new variable in each table with date to distinguish them.
Each table is named according to the scheme ODSHMS.TABLE_202101. The names differ by a different date at the end like ODSHMS.TABLE_202102, ODSHMS.TABLE_202103 etc. The table example looks like this:
ID | VAR |
1 | YES |
2 | NO |
After selecting the tables, adding the date variable from the table name and appending them it should look like below
ID | DATE | VAR |
1 | 202102 | YES |
2 | 202102 | NO |
1 | 202103 | NO |
2 | 202103 | YES |
Can you help me with solution?
Try this.
proc sql noprint;
select cats('ODSHMS.',MEMNAME) into :datasets separated by ' '
from dictionary.tables
where LIBNAME = 'ODSHMS' and MEMNAME like 'TABLE_20%';
quit;
data combined;
set &datasets indsname=DS;
DATE = scan(DS,2,'_');
run;
Hello,
Below code may not be entirely correct as I have to type it "blindly" (i.e. without being able to run it and see the LOG).
But you will surely get the idea.
PROC SQL noprint;
create table work.ds_to_append as
select memname
from dictionary.tables
where libname='ODSHMS' and substr(memname,1,8) = 'TABLE_20';
QUIT;
data _NULL_;
if 0 then set work.ds_to_append nobs=count;
call symput('numobs',strip(put(count,8.)));
STOP;
run;
%PUT &=numobs;
%MACRO append_loop;
%LOCAL i;
PROC DATASETS library=WORK NoList;
delete appendresult / memtype=DATA; run;
QUIT;
%DO i = 1 %TO &numobs.;
data _NULL_;
set work.ds_to_append(firstobs=&i. obs=&i.);
call symput('memname' , strip(memname) );
call symput('date_to_add',substr(strip(memname),7));
run;
data ODSHMS.&memname.;
set ODSHMS.&memname.;
datestamp=&date_to_add.;
run;
PROC APPEND base=work.appendresult data=ODSHMS.&memname.; run;
%END;
QUIT;
%MEND append_loop;
options mprint symbolgen;
%append_loop
/* end of program */
Cheers,
Koen
Try this.
proc sql noprint;
select cats('ODSHMS.',MEMNAME) into :datasets separated by ' '
from dictionary.tables
where LIBNAME = 'ODSHMS' and MEMNAME like 'TABLE_20%';
quit;
data combined;
set &datasets indsname=DS;
DATE = scan(DS,2,'_');
run;
Just FYI, as I see the question has already been solved.
Something like this works as well:
data combined;
set ODSHMS.TABLE_202101-ODSHMS.TABLE_202112 indsname=dsn;
datestamp=scan(dsn,2,'_');
run;
It's called: Using Data Set Lists with SET
... but it's obviously less generic as the previous two solutions.
Koen
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.