06-09-2014 06:26 AM
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?
06-09-2014 07:07 AM
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;
merge base temp;
data list_of_tables (keep=libname memname);
if _n_=1 then do;
call execute('data base; set '||strip(libname)||"."||strip(memname)||'; run;'); /* This takes the first dataset as the base */
else call execute('%add('||strip(libname)||"."||strip(memname)||';');
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.