Date: Thu, 13 Dec 2007 08:49:29 -0800
Reply-To: "Huang, Ya" <Ya.Huang@AMYLIN.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: "Huang, Ya" <Ya.Huang@AMYLIN.COM>
Subject: Re: Data Manipulation question
In-Reply-To: <95b101340712130713h2bce3098nba275a6a7b57c653@mail.gmail.com>
Content-Type: text/plain; charset="windows-1255"
OK, let me try to give a step by step explanation:
proc sql;
select *,case when value < 12 then visit else . end as vlt12
from have
;
subjid visit value vlt12
-------------------------------------
1001 1 15 .
1001 2 14 .
1001 3 13 .
1001 4 12 .
1001 5 10 5
1001 6 9 6
1002 1 14 .
1002 2 13 .
1002 3 12 .
1002 4 10 4
1002 5 9 5
1003 1 13 .
1003 2 9 2
1003 3 12 .
1003 4 12 .
1003 5 13 .
As you can see, this simply query flag all the visit where
value < 12. I call it vlt12
select *,min(case when value < 12 then visit else . end) as v1stlt12
from have
group by subjid
order by subjid,visit
;
If I add min() function and group by clause, I get the following:
subjid visit value v1stlt12
--------------------------------------
1001 1 15 5
1001 2 14 5
1001 3 13 5
1001 4 12 5
1001 5 10 5
1001 6 9 5
1002 1 14 4
1002 2 13 4
1002 3 12 4
1002 4 10 4
1002 5 9 4
1003 1 13 2
1003 2 9 2
1003 3 12 2
1003 4 12 2
1003 5 13 2
The above give me the first visit with value < 12, and because of the min()
function, v1stlt12 was populated for the whole subjid, which is different
from the first step. If we consider this as a new source data,
we can easily flag the visit=v1stlt12 to get the scenario one. As to
scenario two, we just need to find next two visits (v1stlt12+1,+2) and
test if they are both >=12, if so, pick the first visit.
The combined code with flag as below:
proc sql;
select *,visit=v1stlt12 as flag1,
max(visit=v1stlt12+1 and value >= 12)=1 and max(visit=v1stlt12+2 and value >= 12)=1 and visit=v1stlt12+1 as flag2
from( select *,min(case when value < 12 then visit else . end) as v1stlt12
from have group by subjid)
group by subjid
/*having flag1 or flag2*/
order by subjid,visit
;
subjid visit value v1stlt12 flag1 flag2
----------------------------------------------------------
1001 1 15 5 0 0
1001 2 14 5 0 0
1001 3 13 5 0 0
1001 4 12 5 0 0
1001 5 10 5 1 0
1001 6 9 5 0 0
1002 1 14 4 0 0
1002 2 13 4 0 0
1002 3 12 4 0 0
1002 4 10 4 1 0
1002 5 9 4 0 0
1003 1 13 2 0 0
1003 2 9 2 1 0
1003 3 12 2 0 1
1003 4 12 2 0 0
1003 5 13 2 0 0
If you uncomment the having clause, you will get the
subset.
Hope this is clear.
________________________________
From: sas biology [mailto:sasbio@gmail.com]
Sent: Thursday, December 13, 2007 7:14 AM
To: Huang, Ya
Subject: Re: Data Manipulation question
Hi Ya,
Thanks a lot. But honestly , for level of SAS knowledge, this code is so comlex so that I can not do any manipulation. Could you help me change the code little bit so that we can create flag1 and flag2 variables for first and second conditions, respectively.(instead of subsetting)
ie if first condition is met flag1=yes else flag1=no;
if second condition is met flag2=yes else flag2=no;
Thanks a lot for your time.
SB
On 12/13/07, Ya Huang <ya.huang@amylin.com> wrote:
Thanks for the clarification. It now makes sense.
data have;
input subjid visit value;
cards;
1001 1 15
1001 2 14
1001 3 13
1001 4 12
1001 5 10
1001 6 9
1002 1 14
1002 2 13
1002 3 12
1002 4 10
1002 5 9
1003 1 13
1003 2 9
1003 3 12
1003 4 12
1003 5 13
;
proc sql;
select *,v1 as v2
from( select *,min(case when value < 12 then visit else . end) as v1
from have group by subjid)
group by subjid
having visit=v2 or
(max(visit=v2+1 and value >= 12)=1 and
max(visit=v2+2 and value >= 12)=1 and visit=v2+1)
order by subjid,visit
;
quit;
subjid visit value v1 v2
ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ
1001 5 10 5 5
1002 4 10 4 4
1003 2 9 2 2
1003 3 12 2 2
On Thu, 13 Dec 2007 08:54:03 -0500, sas biology < sasbio@GMAIL.COM <mailto:sasbio@GMAIL.COM> > wrote:
>Hi Ya,
>
>You are right. It was my mistake. Please see below for the corrected data.
>Thanks a lot
>
> Hi Group;
>
>I have the folowing data
>
>*
>
>data
>have;
>
>
>
>input
>subjid visit value;
>
>
>
>cards
>;
>
>1001 1 15
>
>1001 2 14
>
>1001 3 13
>
>1001 4 12
>
>1001 5 10
>
>1001 6 9
>
>1002 1 14
>
>1002 2 13
>
>1002 3 12
>
>1002 4 10
>
>1002 5 9
>
>1003 1 13
>
>1003 2 9
>
>1003 3 12
>
>1003 4 12
>
>1003 5 13
>
>;
>
>run;
>
> I need to subset the following records.
>
>1) the record for a patient that has value less than 12 for the first
>time(visit # 5 for subject 1001, #4 for 1002 and #2 for 1003).
>
>2) Also, after that visit(the first time value is less than 12) if two
>consecutive vales are not less than 12 then the first of those two is
>also to be subset(Visit #3 for subject 1003 and no records for 1001 and
>1002).
>
>The number of visits is variable for different patients.
>
>Cam anyone help me how I can go with this?
>
>Thanks
>
>SB
>
>*
>
>
>
>On 12/13/07, Ya Huang <ya.huang@amylin.com> wrote:
>>
>> On Wed, 12 Dec 2007 23:55:47 -0500, sas biology < sasbio@GMAIL.COM> wrote:
>>
>> >Hi Group;
>> >
>> >I have the folowing data
>> >
>> >*
>> >
>> >data have;
>> >
>> >
>> >
>> >input subjid visit value;
>> >
>> >
>> >
>> >cards;
>> >
>> >1001 1 15
>> >
>> >1001 2 14
>> >
>> >1001 3 13
>> >
>> >1001 4 12
>> >
>> >1001 5 10
>> >
>> >1001 6 9
>> >
>> >1002 1 14
>> >
>> >1002 2 13
>> >
>> >1002 3 12
>> >
>> >1002 4 10
>> >
>> >1002 5 9
>> >
>> >1003 1 13
>> >
>> >1003 2 9
>> >
>> >1003 3 11
>> >
>> >1003 4 12
>> >
>> >1003 5 13
>> >
>> >;
>> >
>> >run;
>> >
>> > I need to subset the following records.
>> >
>> >1) the record for a patient that has value less than 12 for the first
>> >time(visit # 5 for subject 1001, #4 for 1002 and #2 for 1003).
>> >
>> >2) Also, after that visit(the first time value is less than 12) if two
>> >consecutive vales are not less than 12 then the first of those two is
>> >also to be subset(Visit #3 for subject 1003 and no records for 1001 and
>> >1002).
>>
>> But visit=3 for 1003, value is 11, which is less than 12?
>>
>> >
>> >The number of visits is variable for different patients.
>> >
>> >Cam anyone help me how I can go with this?
>> >
>> >Thanks
>> >
>> >SB
>> >
>> >*
>>
