Merge all datasets in library (not work)

[fre]

Hi,

I'm referencing to this topic: (which is solved and no reply is possible)

https://communities.sas.com/t5/SAS-Programming/Merge-all-datasets-in-library/td-p/97413

The original question was: 

I have 15 datasets in a library. the data is already sorted. is there a way to merge them all together with out having to write each one out?

The accepted solution was:

proc sql noprint;
   select memname into : names separated by '  '
     from dictionary.tables
    where libname='WORK'; /* note: libname must be in upcase */
data want;
   merge &names;
   by name;
  run;

This all works fine when the tables are in WORK.

My situation is different:  all tables ( > 50) have the same layout and are in libname CAPA.

So I've edited this code to:

proc sql noprint;
   select memname into : names separated by '  '
     from dictionary.tables
       where libname='CAPA'; /* note: libname must be in upcase */

data want;
   set &names;
 run;

When I look to the value of names, it shows a string of all the 50 tables, but without the libname.

What I need is the following string, with libname in front of each table:

CAPA.table1 CAPA.table2 CAPA.table3 ...  CAPA.table50  

instead of:

table1 table2 table3 ... table50 

Is this possible?

[Reeza]

select catx(".", "CAPA", memname) into : names separated by ......

USE the CATX() function to create the correct variable in the query.

[Tom · Accepted solution]

When generating code first think about what code you want to generate.

In your case you want something like:

merge CAPA.table1 CAPA.table2 ...

So obviously you are not going to get that by selection only the MEMNAME value from the metadata. You need to take the LIBNAME value also.

select catx('.',libname,memname)
  into :names separated by ' '

[fre]

Thank you Tom & Reeza, it works like it should now.