Merge all the tables of Library

skygold16 — Mon, 09 Jun 2014 10:26:13 GMT

Hello All,

I have some kind of weird requirement, but have to fulfill.

I want to merge all the tables of the library.

1. I don't know how many tables will be available.

2. Table structure for all the tables in library is same.

3. I want to use coalesce function to find out which is the non-missing variable and that value I have to pick up for a variable of output dataset.

4. Yes, key is same.

Can anyone please help at earliest?

Thanks,

Hatshit

Re: Merge all the tables of Library

RW9 — Mon, 09 Jun 2014 11:07:02 GMT

Well, its a bit vague but what you will need to do is to get a list from SASHELP.VTABLE with a where clause of your libname. This will give you a list of the datasets in that libname. You can then use that call execute a macro which will sort and merge that onto a base table. E.g

%macro add (ds);

proc sort data=&ds. out=temp;

by idvar;

run;

data base;

merge base temp;

by idvar;

run;

%mend add;

data list_of_tables (keep=libname memname);

set sashelp.vtable;

where libname="WORK";

if _n_=1 then do;

call execute('data base; set '||strip(libname)||"."||strip(memname)||'; run;'); /* This takes the first dataset as the base */

end;

else call execute('%add('||strip(libname)||"."||strip(memname)||';');

run;

Of course this assumes a lot of things - datasets are the same, have the same idvar, there is more than 1 etc. You could build an SQL statement using call execute, this is limited to 256 tables though. As for you coalesce, well, in the merge I would rename the temp variables, and then compare.

topic Re: Merge all the tables of Library in SAS Procedures

Merge all the tables of Library

Re: Merge all the tables of Library