Merge all the tables of Library

[skygold16]

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

[RW9]

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.