Date:         Tue, 11 Jan 2011 16:49:17 -0800
Reply-To:     dave crimkey <d_crimkey@YAHOO.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         dave crimkey <d_crimkey@YAHOO.COM>
Subject:      3-way combine
Content-Type: text/plain; charset=us-ascii

Ok, I see that the attachment didn't work so I'm including my code in this
email.


There are three datasets, med, mem and cap.  I need to merge med and mem so that
a record from

either dataset is kept.  At the same time, I need to overwrite a field in med
with values from the field in mem if that field in med is missing or if it is
different from mem.  Then I need to take the cap dataset and merge it to the
combined mem/med dataset, keeping only records from cap that are in the mem/med
dataset.  At the same time (or not if that can't be done) I need to add a field
in mem to the data in cap. The code below does what I want but the real datasets
are huge and I want to do this as efficiently as possible.  Suggestions?

data med;
    input @1 mbr_id $4. @6 clm_cat $4. @10 Prod $4. @16 year 4. @21 quarter 1.
@23 rptg_paid 4.2;
    datalines;
1111 PHY HMO   2008 1 12.0
2222 IP     PPO    2007 1 15.0
3333 OP   LPPO  2008 2 14.0
3333 IP     LPPO  2008 2 15.0
;
run;
data mem;
    input @1 mbr_id $4. @6 clm_cat $4. @10 Prod $4. @16 year 4. @21 quarter 1. ;
    datalines;
1111 PHY LPPO  2008 1
2222 IP     PPO   2007 1
5555 OP   RPPO 2008 2
;
run;
data cap;
    input @1 mbr_id $4. @6 clm_cat $4. @10 year 4. @15 quarter 1. @17 cap_paid
4.2;
    datalines;
1111 CAP 2008 1 1.0
2222 CAP 2007 1 5.0
3333 CAP 2008 2 4.0
3333 CAP 2008 3 5.0
5555 CAP 2008 2 5.0
6666 CAP 2008 2 6.0
;
run;

proc sort data=med (rename=(prod=prod_med));;
    by mbr_id year quarter;
run;
proc sort data=mem;
    by mbr_id year quarter;
run;
proc sort data=cap;
    by mbr_id year quarter;
run;
**** WORKS WITH MED AND MEM -- PUTS MEM DATA ON MED & VICE VERSA;
data medmem medonly;
    merge med (in=a)
          mem (in=b);
    by mbr_id year quarter;
    if a or b;
    if prod ne ' ' and prod ne prod_med then do;
        drop prod_med;
    end;
    else if prod = ' ' and prod_med ne ' ' then do;
        prod=prod_med;
        drop prod_med;
    end ;
    output medmem;
    if a and not b then output medonly;
run;
proc summary nway missing data = medmem;
class mbr_id year quarter prod clm_cat;
var rptg_paid;
output out = med5 (drop = _freq_ _type_)sum=;
run;
proc summary nway missing data=cap;
    class mbr_id year quarter clm_cat;
    var cap_paid;
    output out=capsum (drop=_:) sum= /noinherit;
run;

**** JUST MERGE MEM TO CAP;
**** CAP MEMBERS LIST -- THESE ARE CAP MEMBERS ON MEDMEM with member data ;
proc sql;
    create table memcap as
    select distinct c.*, m.prod from capsum as c join
        mem as m on
        c.mbr_id=m.mbr_id and
        c.year = m.year and
        c.quarter=m.quarter
    order by c.mbr_id, c.year, c.quarter;
quit;
run;
**** THIS CREATES A MERGE OF CAP TO MEDICAL ONLY RECS;
proc sql;
    create table medcap as
    select distinct c.*, m.prod from capsum as c join
        medonly as m on
        c.mbr_id=m.mbr_id and
        c.year = m.year and
        c.quarter=m.quarter
    order by c.mbr_id, c.year, c.quarter;
quit;
run;
data all;
    set memcap
        medmem
        medcap;
run;
** WORKS;
proc summary nway missing data=all;
    class mbr_id clm_cat year quarter;
    var rptg_paid cap_paid;
    output out=alltest (drop=_:) sum= /noinherit;
run;
data all2;
    set all;
    rptg_paid = sum(rptg_paid, cap_paid);
    drop cap_paid;
run;