CODEWITHSUNDEEP
sqlsas
Douce
Douce

Reputation: 5

One-to-many merge with same column names in proc sql

I am looking to join these 2 tables by ordered by ptid and tx_code. what is the ideal way to join so that each observation in dat2 is returned with repeated info from dat1?

data baseline;
input ptid  age gender $ stat stat_date creat tx_code datnum$;
cards;
123 40 M A 02/15/2004 1.2 3 1
456 29 F A 06/05/2009 1.5 2 1
789 53 M D 01/10/2007 2.0 1 1
;
run;

data followup;
 input ptid tx_code stat stat_date wgt hgt  organ datnum $;
cards;
123 3 A 02/15/2004 60 150 ki 2
123 3 A 10/05/2004 75 150 ki 2
456 2 A 06/05/2009 95 173 SKP 2
456 2 A 12/30/2009 95 173 SKP 2
456 2 A 05/05/2010 93 173 SKP 2
456 2 A 10/20/2010 85 173 SKP 2
456 2 D 11/10/2010 . 173 SKP 2
789 1 A 01/10/2007 65 180 pta 2
789 1 A 06/25/2007 60 180 pta 2 
789 1 L 06/25/2008 . . pta 2
;
run;

Upvotes: 0

Views: 321

Answers (1)

Sean
Sean

Reputation: 1120

Join using proc sql. If you only want distinct observations, use "select distinct" instead of "select"

proc sql;
    create table resultant_table as select
        a.*, b.wgt, b.hgt, b.organ, b.stat as stat2, b.stat_date as stat_date2
        from baseline as a
        left join followup as b
        on a.ptid = b.ptid and a.tx_code = input(b.tx_code, 8.);
quit;

Upvotes: 1

Related Questions