Date: Tue, 15 Oct 2002 12:59:19 -0400
Reply-To: diskin.dennis@KENDLE.COM
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: diskin.dennis@KENDLE.COM
Subject: Re: Problem with SQL merge results
Content-type: text/plain; charset=us-ascii
Dave,
After correcting the input statements, I ran this in 8.2 and could not
duplicate your problem. Wong was assigned the GOOD_M as expected.
Regards,
Dennis Diskin
From: Dave Meyer <dmeyer@HOAGHOSPITAL.ORG>@LISTSERV.UGA.EDU> on 10/15/2002
12:13 PM
Please respond to Dave Meyer <dmeyer@HOAGHOSPITAL.ORG>
Sent by: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
To: SAS-L@LISTSERV.UGA.EDU
cc:
Subject: Problem with SQL merge results
Hi SASLers,
I am new to PROC SQL as an alternate way to merge data. I have created
the following test data sets and code - which works as I thought it
would, except for one result. I am trying to merge two db's, as long as
the ID's match and as long as a DOS in table "zMED2" is equal to or in
the range of the first and last DOS in "zMED1" (the one date must be in
the range of the other two dates). Did I write the code correctly? Is
there somthing else that I missed?
(please let me know if I have not been clear)
Here is the code and Output:
DATA WORK.zMED1 ;
INPUT ID_Num LNam $ Ins_Prod $ FST_DOS MMDDYY10. Gender $
LST_DOS MMDDYY10. ;
DATALINES ;
96876 Meyer SEN 05/02/00 M 05/03/00
68424 Kafka SEN 05/01/00 M 05/04/00
96384 Miller SEN 05/01/00 F 05/03/00
20486 Rodis SEN 05/04/00 M 05/04/00
35074 Murphy SEN 05/05/00 M 05/05/00
38420 Jones SEN 05/04/01 M 05/04/01
45876 Miller SEN 05/05/01 M 05/05/01
24587 Giligan SEN 06/10/00 F 06/11/00
99841 Smith SEN 06/10/00 F 06/12/00
88465 Wong HMO 07/01/00 M 07/03/00
54284 Ling HMO 07/02/00 M 07/02/00
77846 Nyguen HMO 07/02/00 F 07/04/00
22158 Heto HMO 08/05/00 M 08/06/00
78458 Bush HMO 08/05/01 M 08/07/01
12345 Willis HMO 08/05/01 F 08/08/01
;
RUN ;
DATA WORK.zMED2;
INPUT ID_Num LNam $ RX_DATE MMDDYY10. CODE $ ;
DATALINES;
96876 Meyer 05/02/00 GOOD_E
24587 Giligan 06/11/00 GOOD_L
88465 Wong 07/02/00 GOOD_M
77846 Nyguen 07/03/00 GOOD_M
12345 Willis 08/09/01 BAD
;
RUN ;
PROC SORT DATA = WORK.ZMED1 ; BY ID_Num FST_DOS ; RUN ;
PROC SORT DATA = WORK.ZMED2 ; BY ID_Num RX_DATE ; RUN ;
PROC SQL ;
CREATE TABLE ZMED_MERGED AS
SELECT *
FROM WORK.ZMED1 AS A ,
WORK.ZMED2 AS B
WHERE A.ID_Num = B.ID_Num
AND B.RX_DATE >= A.FST_DOS
AND B.RX_DATE <= A.LST_DOS ;
QUIT ;
Output:
ID_Num LNam Ins_Prod FST_DOS Gender LST_DOS RX_DATE CODE
24587 Giligan SEN 14771 F 14772 14772 GOOD_L
77846 Nyguen HMO 14793 F 14795 14794 GOOD_M
88465 Wong HMO 14792 M 14794 14793 0 ???
96876 Meyer SEN 14732 M 14733 14732 GOOD_E
I believe that Wong's CODE should be "Good_M" too! Am I missing
somthing?
TIA for any help advice you have.
Dave
--
Posted via Mailgate.ORG Server - http://www.Mailgate.ORG
