topic Re: Merging data by two variables in SAS Procedures

Merging data by two variables

SasFellow331 — Fri, 31 Oct 2014 00:13:01 GMT

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

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

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

Re: Merging data by two variables

Haikuo — Fri, 31 Oct 2014 02:25:02 GMT

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;

Re: Merging data by two variables

Ksharp — Fri, 31 Oct 2014 07:11:39 GMT

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

Re: Merging data by two variables

SasFellow331 — Sat, 01 Nov 2014 21:49:24 GMT

thank you both