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
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.