Join Table with dynamic Table Name

[SuzaneM]

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.

[ballardw]

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.

[SuzaneM]

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.

[BrunoMueller]

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;

[RW9]

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.

[Kurt_Bremser]

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;