topic Re: SAS PROC SQL LEFT JOIN in SAS Studio

SAS PROC SQL LEFT JOIN

Kamohelo_A — Mon, 26 Jul 2021 10:29:00 GMT

Morning

I need urgent help, how do you do a join or data step merge in sas between two tables that have multiple columns that must be merged otherwise if you merge on just the one result the data table that gets created breaks and provided a number of rows that is higher than the initial table to which you do a left join.

Note that I need to merge at most three columns across two tables that share the exact same three columns with the table in the left join having more columns than the other table.

Re: SAS PROC SQL LEFT JOIN

PaigeMiller — Mon, 26 Jul 2021 10:35:52 GMT

Example:

proc sql;
     create table want as select
          a.*,b.var1,b.var2,b.var3,b.var4
          /* type whatever variable names you want here instead of the fake names I used */
     from dataset1 a left join dataset2 b 
         on a.id1=b.id1 and a.id2=b.id2 and a.id3=b.id3;
         /* type whatever variable names you want here instead of the fake names I used */
quit;

Re: SAS PROC SQL LEFT JOIN

Kamohelo_A — Mon, 26 Jul 2021 11:09:40 GMT

Never mind. I resolved it using the following:

PROC SQL;
CREATE TABLE DATA_1 AS
SELECT DATA_INITIAL_1.* , OTHER_DATA.COLUMN5
FROM DATA_INITIAL_1
LEFT JOIN
OTHER_DATA
ON DATA_INITIAL_1.COLUMN1 = OTHER_DATA.COLUMN1
AND DATA_INITIAL_1.COLUMN2 = OTHER_DATA.COLUMN2
AND DATA_INITIAL_1.COLUMN3 = OTHER_DATA.COLUMN3
AND DATA_INITIAL_1.COLUMN4 = OTHER_DATA.COLUMN4;
QUIT;

Re: SAS PROC SQL LEFT JOIN

Kamohelo_A — Mon, 26 Jul 2021 11:11:08 GMT

THANKS FOR THE HEADS UP