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;
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;
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;
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.
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;
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!
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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.