DATA Step, Macro, Functions and more

Join Table with dynamic Table Name

Reply
New Contributor
Posts: 2

Join Table with dynamic Table Name

Hi,

I am trying to join two tables  based on the columnValue obtained from one Table

Table A

id     TableName

1       TableA

2       TableB

3       TableA

4       TableB

5       TableC

Proc SQL;

CREATE 

TABLE output

AS

SELECT A.*,

FROM Table A

LEFT JOIN &A.TABLENAME B

ON A.ID = B.ID;

QUIT;

I am not sure how I could do this in SAS. Any help regarding this would be helpful

Thanks in advance.

Super User
Posts: 10,552

Re: Join Table with dynamic Table Name

You might need to provide a few more details of what you are trying to accomplish. It almost looks as if you want the use the name of a table that is stored in a data set and then select some data associated with that table.

If that is the case you may be looking for Call Execute in a data step.

New Contributor
Posts: 2

Re: Join Table with dynamic Table Name

Yes, I want to use the name of a table stored in a data set based on the ids and select data associated with the table.

SAS Super FREQ
Posts: 685

Re: Join Table with dynamic Table Name

I do not know what exactly your query needs to be, write your query into a file while reading from the first table and then use %INCLUDE to run the code written to the file. See sample below:

data have;
  infile cards dlm="," dsd;
 
input
    id :
8.
    tableName :
$32.
  ;
  cards;
1,TableA
2,TableB
3,TableA
4,TableB
1,TableC
5,TableC
;

%macro createtables(name=);

  data &name;
    do id =
1 to 5;
      someValue = catx(
"_", id, "&name");
      output;
    end;
  run;

%mend;

%
createtables(name=tableA)
%
createtables(name=tableB)
%
createtables(name=tableC)

filename mycode temp;

data _null_;
  set have end=last;
  file myCode;
  length query $ 1024;
  query = catx(
" ",
   
'select a.* from'
    , strip(tableName)
    ,
'as a'
    ,
'where a.id = '
    , id
    );

 
if _n_ = 1 then do;
   
put "proc sql;";
   
put "create table want2 as";
   
put query;
  end;
 
else do;
   
put "outer union corresponding";
   
put query;
  end;

 
if last = 1 then do;
   
put ";quit;";
 
end;
run;

%include myCode / source2;
filename myCode clear;
Super User
Super User
Posts: 7,432

Re: Join Table with dynamic Table Name

Hi,

There is a couple of functions for this type of thing.  call sub() and call execute().  I tend to focus on the second:

data _null_;

     set tablea;

     call execute('proc sql;

                              create table WORK.OUTPUT as

                              select      A.*,

                              from        WORK.TABLEA A

                              left join    '||strip(tablename)||' B

                              on A.ID = B.ID;

                         quit;');

run;

What the above does is for every record in tablea, creates a text string which is sent to the compiler.   The text in the call execute() procedure can be formed by any normal SAS string processing, in this case I concatenate the tablename from the dataset into the sql clause.

Note I haven't checked the logic in your SQL clause, just copied and pasted.

Super User
Posts: 6,982

Re: Join Table with dynamic Table Name

I try to define your problem:

TableX has ID and Tname

TableA, TableB and TableC have ID and some_data

I suggest something like

data want (keep=ID some_data);

merge

  TableX (in=x)

  TableA (in=a rename=(some_data=some_data_a))

  TableB (in=b rename=(some_data=some_data_b))

  TableC (in=c rename=(some_data=some_data_c))

;

by ID;

if x;

select (Tname);

  when ('TableA') do; if a then some_data = some_data_a; end;

  when ('TableB') do; if b then some_data = some_data_b; end;

  when ('TableC') do; if c then some_data = some_data_c; end;

  otherwise;

end;

run;

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Ask a Question
Discussion stats
  • 5 replies
  • 510 views
  • 0 likes
  • 5 in conversation