Date:         Tue, 13 Dec 2005 12:06:24 +1100
Reply-To:     Scott Bass <usenet739_yahoo_com_au@ALFREDO.CC.UGA.EDU>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Scott Bass <usenet739_yahoo_com_au@ALFREDO.CC.UGA.EDU>
Subject:      SQL Question
Comments: To: sas-l@uga.edu

Hi,

Some test code:

* test data: clinresp=1 --> cure, clinresp=2 --> failure ;
data test;
   length subjid visit clinresp 8;
   infile cards truncover;
   input subjid visit clinresp;
   cards;
1  3  1
1  4  1

2  3  1
2  4  2

3  3  2
3  4  2

4  3  1
4  4  1

5  3  1
5  4  2

6  3  2
6  4  2
;
run;

data test;
   set test;
   if subjid ne .;
run;

proc sql;
   create view relapse as
   select subjid, visit
      from test
      where subjid in
         (select subjid
            from test
            where visit=3 and clinresp=1
         )
         and visit=4 and clinresp=2;
quit;

data test2;
   merge
      test
      relapse (in=x)
   ;
   by subjid visit;
   relapse=x;
run;

Is there a way to combine the sql and data step into one sql step?  The
above works but I wanted to know if I could tighten it a bit (and learn some
SQL in the process).  The output from the SQL should be identical to test2.

Let me know if you need further explanation on the logic.

Thanks,
Scott