|
Maybe the following could help:
DATA test1;
INPUT @ 1 CASEID $3.
@ 5 ASSDT DATE9.
@15 RELDAY
@17 TIME $;
LABEL CASEID = 'Patient CASEID NO.'
ASSDT = 'Assessment Date'
RELDAY = 'Relative Day'
TIME = 'Assessment Time';
FORMAT ASSDT MMDDYY10.;
DATALINES;
001 15NOV1998 1 10:00
001 15NOV1998 1 22:00
001 16NOV1998 7 04:00
001 16NOV1998 7 16:00
001 17NOV1998 6 09:00
001 19NOV1998 2 08:00
001 19NOV1998 2 20:00
002 11AUG1998 5 04:00
002 11AUG1998 5 16:00
002 12AUG1998 6 03:00
002 12AUG1998 6 15:00
002 13AUG1998 1 12:00
003 02SEP1998 9 01:00
003 02SEP1998 9 13:00
003 03SEP1998 2 02:00
003 03SEP1998 2 14:00
003 04SEP1998 3 02:00
003 04SEP1998 3 14:00
;
proc print data=test2;
run ;
DATA test2;
INPUT @ 1 CASEID $3.
@ 5 EVADT DATE9.
@15 Trough;
LABEL CASEID = 'Patient CASEID NO.'
EVADT = 'Evaluation Date' ;
FORMAT EVADT MMDDYY10.;
DATALINES;
001 15NOV1998 10.0
001 17NOV1998 10.2
001 21NOV1998 10.7
002 13AUG1998 10.5
002 16AUG1998 11.0
002 19AUG1998 12.4
003 05SEP1998 10.3
003 06SEP1998 10.5
;
/*proc sql ;*/
/*create table test3 as*/
/*select distinct Test2.*, Max( Test1.ASSDT ) as ASSDT format = date9.,*/
/*Test1.Time as Time*/
/*from Test2 as Test2*/
/* left join*/
/* Test1 as test1*/
/* on ( Test2.CaseId = Test1.CaseId )*/
/* and*/
/* ( ( Test2.EVADT - 2) <= Test1.ASSDT < Test2.EVADT )*/
/* group by test2.CaseId , test2.EVADT*/
/* order by CaseId , EVADT ;*/
/*quit ;*/
data test3;merge test1 test2;by CaseId;
if ( EVADT - 2) <= ASSDT < EVADT;
run;
proc sort data=test3;
by caseid, evadt;
run ;
proc print data=test3;
run ;
----- Original Message -----
From: "BB" <bhavin.busa@CUBIST.COM>
To: <SAS-L@LISTSERV.UGA.EDU>
Sent: Tuesday, June 20, 2006 12:31 AM
Subject: Creating dataset with condition using PROC SQL
> hello all,
>
> I have one condition in PROC SQL that I am not able to get. I have
> included the datasets and program below.
>
> The condition is that the value of ASSDT in dataset TEST3 should be one
> day prior to EVADT, ELSE two days prior to EVADT. If the condition is not
> satisfied the ASSDT should be set to missing for that observation and for
> that given caseid.
>
> I want to create dataset test3 in such a way that each observation for a
> given caseid in dataset test2 should have value of ASSDT from dataset
> test1 and ALSO the value of time variable for its corresponding ASSDT.
>
> Please see the O/P I am getting after using the code below. What it's
> doing here is giving me the right ASSDT in the O/P dataset but it is also
> giving me TIME for all the conditions satisfied by ( ( Test2.EVADT - 2) <=
> Test1.ASSDT < Test2.EVADT), i.e. when ASSDT is less then EVADT by 1 and 2.
> Since, we are using Max(Test1.ASSDT ) as ASSDT format = date9. in the
> select statement, it is giving me the maximum value of ASSDT in the O/P
> but not the corresponding TIME value. I have attached the O/P I need to
> get.
>
> I would really appreciate if you guys can review it and give me your
> suggestion.
>
> Thanks a lot.
>
> BB
>
>
> DATA test1;
> INPUT @ 1 CASEID $3.
> @ 5 ASSDT DATE9.
> @15 RELDAY
> @17 TIME $;
> LABEL CASEID = 'Patient CASEID NO.'
> ASSDT = 'Assessment Date'
> RELDAY = 'Relative Day'
> TIME = 'Assessment Time';
> FORMAT ASSDT MMDDYY10.;
> DATALINES;
> 001 15NOV1998 1 10:00
> 001 15NOV1998 1 22:00
> 001 16NOV1998 7 04:00
> 001 16NOV1998 7 16:00
> 001 17NOV1998 6 09:00
> 001 19NOV1998 2 08:00
> 001 19NOV1998 2 20:00
> 002 11AUG1998 5 04:00
> 002 11AUG1998 5 16:00
> 002 12AUG1998 6 03:00
> 002 12AUG1998 6 15:00
> 002 13AUG1998 1 12:00
> 003 02SEP1998 9 01:00
> 003 02SEP1998 9 13:00
> 003 03SEP1998 2 02:00
> 003 03SEP1998 2 14:00
> 003 04SEP1998 3 02:00
> 003 04SEP1998 3 14:00
> ;
> proc print data=test2;
> run ;
>
> DATA test2;
> INPUT @ 1 CASEID $3.
> @ 5 EVADT DATE9.
> @15 Trough;
> LABEL CASEID = 'Patient CASEID NO.'
> EVADT = 'Evaluation Date' ;
> FORMAT EVADT MMDDYY10.;
> DATALINES;
> 001 15NOV1998 10.0
> 001 17NOV1998 10.2
> 001 21NOV1998 10.7
> 002 13AUG1998 10.5
> 002 16AUG1998 11.0
> 002 19AUG1998 12.4
> 003 05SEP1998 10.3
> 003 06SEP1998 10.5
> ;
>
> proc sql ;
> create table test3 as
> select distinct Test2.*, Max( Test1.ASSDT ) as ASSDT format = date9.,
> Test1.Time as Time
> from Test2 as Test2
> left join
> Test1 as test1
> on ( Test2.CaseId = Test1.CaseId )
> and
> ( ( Test2.EVADT - 2) <= Test1.ASSDT < Test2.EVADT )
> group by test2.CaseId , test2.EVADT
> order by CaseId , EVADT ;
> quit ;
>
>
> proc sort data=test3;
> by caseid, evadt;
> run ;
>
> proc print data=test3;
> run ;
>
>
> The output I am getting is this:
>
>
> Obs CASEID EVADT Trough ASSDT TIME
>
> 1 001 11/15/1998 10.0 .
> 2 001 11/17/1998 10.2 16NOV1998 04:00
> 3 001 11/17/1998 10.2 16NOV1998 10:00
> 4 001 11/17/1998 10.2 16NOV1998 16:00
> 5 001 11/17/1998 10.2 16NOV1998 22:00
> 6 001 11/21/1998 10.7 19NOV1998 08:00
> 7 001 11/21/1998 10.7 19NOV1998 20:00
> 8 002 08/13/1998 10.5 12AUG1998 03:00
> 9 002 08/13/1998 10.5 12AUG1998 04:00
> 10 002 08/13/1998 10.5 12AUG1998 15:00
> 11 002 08/13/1998 10.5 12AUG1998 16:00
> 12 002 08/16/1998 11.0 .
> 13 002 08/19/1998 12.4 .
> 14 003 09/05/1998 10.3 04SEP1998 02:00
> 15 003 09/05/1998 10.3 04SEP1998 14:00
> 16 003 09/06/1998 10.5 04SEP1998 02:00
> 17 003 09/06/1998 10.5 04SEP1998 14:00
>
> What I am expecting is something like this:
>
> Obs CASEID EVADT Trough ASSDT TIME
>
> 1 001 11/15/1998 10.0 .
> 2 001 11/17/1998 10.2 16NOV1998 04:00
> 3 001 11/17/1998 10.2 16NOV1998 16:00
> 4 001 11/21/1998 10.7 19NOV1998 08:00
> 5 001 11/21/1998 10.7 19NOV1998 20:00
> 6 002 08/13/1998 10.5 12AUG1998 03:00
> 7 002 08/13/1998 10.5 12AUG1998 15:00
> 8 002 08/16/1998 11.0 .
> 9 002 08/19/1998 12.4 .
> 10 003 09/05/1998 10.3 04SEP1998 02:00
> 11 003 09/05/1998 10.3 04SEP1998 14:00
> 12 003 09/06/1998 10.5 04SEP1998 02:00
> 13 003 09/06/1998 10.5 04SEP1998 14:00
>
| 
