Date:         Tue, 15 Oct 2002 12:48:43 -0400
Reply-To:     "Laurel A. Copeland" <lacop@UMICH.EDU>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Laurel A. Copeland" <lacop@UMICH.EDU>
Subject:      Re: Problem with SQL merge results

Hi Dave.
Your code did not produce valid dates for me in the 1st ds. See below.
It produced the value of 0 for Wong in 2nd ds, hence, 0 in output ds also.
Check your data in.
Add col specs whenever you use specific formats like these.
I got 2 warnings on the SQL. Specify sources of vars to eliminate those.
Add formats to dates in the SQL.
Do you still have a problem?
HTH,
Laurel

*dave 10-15-02;
DATA WORK.zMED1 ;
        INPUT ID_Num LNam $ Ins_Prod $ @24 FST_DOS MMDDYY10. @41 Gender $
        @48 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 ;
proc print; format fst_dos lst_dos date9.; run;

DATA WORK.zMED2;
        INPUT ID_Num LNam $ RX_DATE MMDDYY10. @33 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 print; format rx_date date9.; run;

PROC SQL ;
        CREATE TABLE ZMED_MERGED AS
        SELECT a.id_num, a.lnam, a.Ins_Prod,
        a.FST_DOS format=date9., a.Gender,
        a.LST_DOS format=date9., b.rx_date format=date9., b.code
          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 ;
proc print; run;