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.
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.
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.
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:
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.
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.