topic Re: Passing a column with table names in a data step in SAS Programming

Passing a column with table names in a data step

carles — Wed, 24 Jul 2019 14:58:41 GMT

Let's assume I have two tables and I want to join them as following:

data Table1;
   input var$ ;
   datalines;
hello1
hello2
hello3
;
run;
data Table2;
   input var$ ;
   datalines;
hello1
hello2
hello4
;
run;
data Table3;
set Table1 Table2;
run;

However, let's imagine I have the names of the tables listed in a data table as follows:

data TableNames;
   length Tablenames $ 12;
   input Tablenames $ ;
   datalines;
Table1
Table2
;

How could I do it to pass the values of that column to get the same as in Table 3 ?

P.D: I can only use base SAS

Thank you so much for the help in advanced;

Re: Passing a column with table names in a data step

Shmuel — Thu, 25 Jul 2019 13:37:21 GMT

If I understand you correctly:

data TableNames;
   length Tablenames $ 12;
   input Tablenames $ ;
   if tablenames = '*END*' then
      call symput('nrows', trim(_N_ -1));
   else
      call symput('Table'!!trim(_N_), trim(tablenames));
   datalines;
Table1
Table2
*END*
;
run;

%macro set_all;
   data want;
      set
      %do i=1 %to &nrows;
          &&Table&i
      %end;              /* fixed typos */
      ;
run;
%mend;
%set_all;

@Tom's solution is shorter and more elegant, using sql:

proc sql noprint;
select tablenames
  into :tablelist separated by ' '
  from tablenames
;
quit;

data want;
  set &tablelist ;
run;

Re: Passing a column with table names in a data step

Tom — Wed, 24 Jul 2019 15:23:19 GMT

As long as the list of table names is small enough to fit in a macro variable (limit is 65K characters) it is probably easiest to just make a macro variable and use the macro variable to generate the code.

proc sql noprint;
select tablenames
  into :tablelist separated by ' '
  from tablenames
;
quit;

data want;
  set &tablelist ;
run;

Re: Passing a column with table names in a data step

VDD — Wed, 24 Jul 2019 15:42:53 GMT

libname fart "&mypath.";
proc sql noprint ;
select 'FART.'|| memname into :mylist separated by ' '
from dictionary.tables
where libname='FART' and nobs>0
;
quit;


data want;
  set &mylist. ;
run;

Re: Passing a column with table names in a data step

carles — Thu, 25 Jul 2019 06:51:32 GMT

I liked this answer due to the fact (1) It was the first answer (2) I like the SAS approach. As a matter of improvement: Could you edit your answer such that you also add the answer of the colleague below ?:). I thought his answer was also good and easier to read.

Re: Passing a column with table names in a data step

carles — Thu, 25 Jul 2019 06:53:53 GMT

Thank you so much for the answer. I have written to guy above to include in his answer yours such that overall answer gets improved. I have used though your answer becasue of easiness in understanding though generaly I prefer the SAS approach. For that, thank you!