Merge list of datasets their names and libraries exist in maindataset in sas

[Maya_QC]

I need to merge matching datasets from  a list of libraries , their names existing in a main dataset with 2 column libVar and fileVar like :

libVar   filevar

lib1      fn1
lib2      fn1
lib3      fn1

lib1      fn2
lib2      fn2

lib1      fn3
lib2      fn3
lib3      fn3

So after the merge , I will get  3 datasets : fn1, fn2 and fn3 in new library. I tried to resolve it by using first and last variables but without sucess.

Thanks in advance for your help.

[Patrick]

What kind of merge? Or is it concatenation (append)? Do the same named tables have the same structure (same columns)?

If the data volumes are high: Could you create a view instead of replicating the data?

Can you share what you've already tried and eventually some of your code (whether working or not).

[JerryLeBreton · Accepted solution]

In the absence of much detail, this might get you on the right track.

I've taken your approach of using first. / last. processing and call execute.  There are other approaches of course.

I've assumed for simplicity that you want to append rather than merge the tables:

/* Create a macro to combine a string of datasets, using the last table name for the results table */

%macro merger(file_list);

data %scan(&file_list,-1,%str(.));

set &file_list;

run;

%mend;

　

/* Sort by the fileVar values */

proc sort data=mainDataset;

by fileVar libVar ;

run;

data _null_;

set mainDataset;

by fileVar;

length file_list $1000;

retain file_list '';

/* Create a string with all the lib.file names for a given file */

file_list = catx(' ', file_list, cats(libVar, '.', fileVar));

if last.fileVar then

do;

call execute('%merger('||file_list||')');

file_list = '';

end;

run;

Hope this helps.

[Maya_QC]

Thank you so much for your help , so appreciated

[Astounding]

As was noted, it depends on what you mean by "merge".  But here is one way:

data _null_;

set have;

call execute('proc append data=' || trim(libvar) || '.' || filevar || ' base=lib4.' || filevar || '; run;');

run;

This assumes you have libname statements for the existing folders (lib1, lib2, lib3) as well as for the new one (lib4) that is supposed to hold the results.

Good luck.