BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
carles
Fluorite | Level 6

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; 

1 ACCEPTED SOLUTION

Accepted Solutions
Shmuel
Garnet | Level 18

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;

 

View solution in original post

5 REPLIES 5
Shmuel
Garnet | Level 18

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;

 

carles
Fluorite | Level 6

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.

Tom
Super User Tom
Super User

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;
carles
Fluorite | Level 6

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!

VDD
Ammonite | Level 13 VDD
Ammonite | Level 13
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;

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 736 views
  • 3 likes
  • 4 in conversation