Help using Base SAS procedures

Merging data by two variables

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 7
Accepted Solution

Merging data by two variables

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

00101Jan2003
00102Jan2003
00215Mar2003
00201Mar2003
00301Apr2003
00419Mar2003

AE

00131Dec2002Headache
00102Jan2003Blurry Vision
00102Jan2003Anxiety
00202Mar2003Migraine
00201Mar2003Constipation
00215Mar2004Athlete’s Foot
00302Apr2003Depression
00302Apr2003Rash

Final

00101Jan200302Jan2003Blurry Vision
00101Jan200302Jan2003Anxiety
00201Mar200301Mar2003Constipation
00201Mar200302Mar2003Migraine
00301Apr200302Apr2003Depression
00301Apr200302Apr2003Rash

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/)


Accepted Solutions
Solution
‎10-31-2014 03:11 AM
Super User
Posts: 9,681

Re: Merging data by two variables

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

View solution in original post


All Replies
Respected Advisor
Posts: 3,124

Re: Merging data by two variables

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;

Solution
‎10-31-2014 03:11 AM
Super User
Posts: 9,681

Re: Merging data by two variables

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

Occasional Contributor
Posts: 7

Re: Merging data by two variables

thank you both

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 284 views
  • 0 likes
  • 3 in conversation