Automatically select and append tables from the library

[PatrykSAS]

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?

[sbxkoenk]

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

[Athenkosi · Accepted 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;

[PatrykSAS]

Works nice and easy, Thank you

[sbxkoenk]

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