Merging data by two variables

[SasFellow331]

i found this example online, and i am having a hard time solving it. This is what i have so far:

data Dose;                                                                                                                             

input Patient Dosedate $9.;                                                                                                            

cards;                                                                                                                                 

001 01Jan2003                                                                                                                          

001 02Jan2003                                                                                                                          

002 15Mar2003                                                                                                                          

002 01Mar2003                                                                                                                          

003 01Mar2003                                                                                                                          

004 19Mar2003                                                                                                                          

run;                                                                                                                                   

data AE;                                                                                                                               

input Patient AeStartDate $10.  AeText $15.;                                                                                           

cards;                                                                                                                                 

001 31Dec2002 Headache                                                                                                                 

001 02Jan2003 Blurry Vision                                                                                                            

001 02Jan2003 Anxiety                                                                                                                  

002 02Mar2003 Migraine                                                                                                                 

002 01Mar2003 Constipation                                                                                                             

002 15Mar2003 Athelete's Foot                                                                                                          

003 02Apr2003 Depression                                                                                                               

003 02Apr2003 Rash                                                                                                                     

run;                                                                                                                                   

data Final4;                                                                                                                           

merge Dose(in=indemog)                                                                                                                 

Ae(in=invisit);                                                                                                          

by patient;                                                                                                                            

if indemog=1 and invisit=1;                                                                                                            

run;

Here is the original Data with the proposed Procedure below it. (All help is greatly appreciated)

Dose

001	01Jan2003
001	02Jan2003
002	15Mar2003
002	01Mar2003
003	01Apr2003
004	19Mar2003

AE

001	31Dec2002	Headache
001	02Jan2003	Blurry Vision
001	02Jan2003	Anxiety
002	02Mar2003	Migraine
002	01Mar2003	Constipation
002	15Mar2004	Athlete’s Foot
003	02Apr2003	Depression
003	02Apr2003	Rash

Final

001	01Jan2003	02Jan2003	Blurry Vision
001	01Jan2003	02Jan2003	Anxiety
002	01Mar2003	01Mar2003	Constipation
002	01Mar2003	02Mar2003	Migraine
003	01Apr2003	02Apr2003	Depression
003	01Apr2003	02Apr2003	Rash

Questions

1. Using SAS procedures and data steps, combine the Dose and AE datasets together to get the Final dataset. The Final dataset should include adverse events that occurred on a dosing date or one day after a dosing date.

(http://technico.qnownow.com/sas-project-2-clinical-trials-sas-technical-problem-solve-diy/)

[Haikuo]

Proc SQL seems to be straightforward:

data Dose;

     infile cards dlm='09'x;

     input id $3. Dose_dt :date9.;

     format dose_dt date9.;

     cards;

001  01Jan2003

001  02Jan2003

002  15Mar2003

002  01Mar2003

003  01Apr2003

004  19Mar2003

;

data AE;

     infile cards dlm='09'x;

     input id $3. AE_dt :date9. Symptom :&$20.;

     format ae_dt date9.;

     cards;

001  31Dec2002  Headache

001  02Jan2003  Blurry Vision

001  02Jan2003  Anxiety

002  02Mar2003  Migraine

002  01Mar2003  Constipation

002  15Mar2004  Athlete’s Foot

003  02Apr2003  Depression

003  02Apr2003  Rash

;

proc sql;

     select distinct a.id, dose_dt, ae_dt, symptom

           from dose a, ae b

                where a.id=b.id and ae_dt between dose_dt and dose_dt+1

                     group by a.id, ae_dt, symptom

                           having dose_dt=min(dose_dt)

                                order by a.id, ae_dt, symptom desc

     ;

quit;

[Ksharp · Accepted solution]

If I understood what you mean.

data Dose;
     infile cards truncover;
     input patient $3. Dose_dt :date9.;
     format dose_dt date9.;
     cards;
001  01Jan2003
001  02Jan2003
002  15Mar2003
002  01Mar2003
003  01Apr2003
004  19Mar2003
;
data AE;
     infile cards truncover;
     input patient : $3. AE_dt :date9. Symptom &$20.;
     format ae_dt date9.;
     cards;
001  31Dec2002  Headache
001  02Jan2003  Blurry Vision
001  02Jan2003  Anxiety
002  02Mar2003  Migraine
002  01Mar2003  Constipation
002  15Mar2004  Athlete's Foot
003  02Apr2003  Depression
003  02Apr2003  Rash
;
run;

data temp_dose;
 set dose;
 _Dose_dt=Dose_dt;
 output;
 Dose_dt=Dose_dt+1;output;
 format _dose_dt date9.;
run;
proc sort data=temp_dose nodupkey;by patient Dose_dt;run;
proc sort data=Ae ;by patient AE_dt ;run;
data Final4;                                                                                                                           
merge temp_dose(in=indemog rename=(Dose_dt=dt))                                                                                                                 
Ae(in=invisit rename=(AE_dt=dt));                                                                                                          
by patient dt;                                                                                                                            
if indemog=1 and invisit=1;                                                                                                            
run;

Xia Keshan

Message was edited by: xia keshan Fix some errors

Message was edited by: xia keshan

[SasFellow331]

thank you both