topic Re: Join Table with dynamic Table Name in SAS Programming

Join Table with dynamic Table Name

SuzaneM — Tue, 17 Feb 2015 19:34:01 GMT

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

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.

Re: Join Table with dynamic Table Name

ballardw — Tue, 17 Feb 2015 20:01:39 GMT

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.

Re: Join Table with dynamic Table Name

SuzaneM — Tue, 17 Feb 2015 20:06:58 GMT

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.

Re: Join Table with dynamic Table Name

BrunoMueller — Wed, 18 Feb 2015 10:11:03 GMT

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; 

Re: Join Table with dynamic Table Name

RW9 — Wed, 18 Feb 2015 10:27:41 GMT

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.

Re: Join Table with dynamic Table Name

Kurt_Bremser — Wed, 18 Feb 2015 11:42:48 GMT

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;