Date: Wed, 5 Sep 2007 17:37:38 -0400
Reply-To: Ya Huang <ya.huang@AMYLIN.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Ya Huang <ya.huang@AMYLIN.COM>
Subject: Re: Merge question
Here is one without using proc sql:
Data one;
input ID Visit :ddmmyy10.;
cards;
19 31/07/2000
41 02/08/2000
52 02/08/2000
19 14/08/2000
;
Data two;
input Ref_date :ddmmyy10. Grp;
cards;
31/07/2000 1
24/07/2000 0
17/07/2000 0
10/07/2000 0
03/07/2000 0
02/08/2000 1
09/08/2000 0
16/08/2000 0
23/08/2000 0
30/08/2000 0
14/08/2000 1
07/08/2000 0
21/08/2000 0
28/08/2000 0
;
data two;
set two;
retain firstdt;
if grp=1 then do; group+1; firstdt=ref_date; end;
run;
proc transpose data=two out=three;
by group firstdt;
var ref_date;
run;
proc sort data=one;
by visit id;
run;
data four;
merge one three (rename=(firstdt=visit));
by visit;
run;
proc transpose data=four out=five (drop=_name_ where=(^missing(ref_date)));
by id notsorted;
var col:;
run;
data five;
set five;
by id notsorted;
if first.id then grp=1; else grp=0;
run;
proc print;
format ref_date ddmmyy10.;
run;
ID Ref_date gr
19 31/07/2000 1
19 24/07/2000 0
19 17/07/2000 0
19 10/07/2000 0
19 03/07/2000 0
41 02/08/2000 1
41 09/08/2000 0
41 16/08/2000 0
41 23/08/2000 0
41 30/08/2000 0
52 02/08/2000 1
52 09/08/2000 0
52 16/08/2000 0
52 23/08/2000 0
52 30/08/2000 0
19 14/08/2000 1
19 07/08/2000 0
19 21/08/2000 0
19 28/08/2000 0
On Wed, 5 Sep 2007 20:42:07 -0000, olana98@GOOGLEMAIL.COM wrote:
>Dear SAS Group,
>
>I wanted to merge two data sets. One contains patient information-
>date of hospital visit and others. Commonly, there are several daily
>visits leading to multiple observations in a day. The second data set
>contains an exposure parameter measured on the day of visit and
>several controls spaced weeks apart. The plan is to merge each
>hospital visit to several exposure dates. I have copied a sample data
>set. The ID is not unique in the data set as a patient may visit a
>hospital more than once. It appears it is many to many merging and I
>attempt with ordinary merging did not produce the required result.Any
>suggestion is appreciated. Thanks and best wishes
>
>Data set one
>ID Visit
>19 31/07/2000
>41 02/08/2000
>52 02/08/2000
>19 14/08/2000
>
>Data set two
>
>Ref_date Grp What I wanted to achieve
>
>31/07/2000 1 19 31/07/2000 1
>24/07/2000 0 19 24/07/2000 0
>17/07/2000 0 19 17/07/2000 0
>10/07/2000 0 19 10/07/2000 0
>03/07/2000 0 19 03/07/2000 0
>02/08/2000 1 41 02/08/2000 1
>09/08/2000 0 41 09/08/2000 0
>16/08/2000 0 41 16/08/2000 0
>23/08/2000 0 41 23/08/2000 0
>30/08/2000 0 41 30/08/2000 0
>14/08/2000 1 52 02/08/2000 1
>07/08/2000 0 52 09/08/2000 0
>21/08/2000 0 52 16/08/2000 0
>28/08/2000 0 52 23/08/2000 0
> 52 30/08/2000 0
> 19 14/08/2000 1
> 19 07/08/2000 0
> 19 21/08/2000 0
> 19 28/08/2000 0
