topic Re: SORT AND FLAG IN SAS in SAS Procedures

SORT AND FLAG IN SAS

robertrao — Thu, 01 Aug 2013 14:38:50 GMT

Hi TEAM,

I am trying to check if a tablet is taken prior to surgery or post surgery

If tablet is taken prior to surgery then i want the closest one to be flagged as PRIOR!

At the same time if an ID have taken it post surgery then I want to flag it in another variable as POST

And a single record for ID????

If Surgery and tablet on the same day then its not prior but POST

HAVE

ID     TABLET               SURG
600 15MAY2013      .
600 16MAY2013      .
600 19MAY2013      .

885 19JUN2013   19JUL2013
885 19JUN2013   19JUL2013
885 20JUN2013   19JUL2013
885 30JUN2013   19JUL2013
885 03JUL2013   19JUL2013
885 12JUL2013   19JUL2013
885 13JUL2013   19JUL2013

405 24MAY2013   24MAy2013
405 29MAY2013   24MAy2013
405 30MAY2013   24MAy2013
405 31MAY2013   24MAy2013

500 16APR2013   18APR2013
500 16APR2013   18APR2013
500 16APR2013   18APR2013
500 17APR2013   18APR2013
500 17APR2013   18APR2013
500 17APR2013   18APR2013
500 18APR2013   18APR2013
500 18APR2013   18APR2013
500 19APR2013   18APR2013
500 20APR2013   18APR2013

415 19JUN2013 19JUL2013

WANT

SURG_DATE PRIOR TAB DATE PRIOR_flag POST_TAB_DATE POST_flag

600 . . . .

885 19JUL2013 13JUL2013 yes 19JUN2013 yes

405 24MAy2013 . . 24MAY2013 yes

500 18APR2013 17APR2013 yes 19APR2013 yes

414 19JUL2013 . . 19JUN2013 yes

Re: SORT AND FLAG IN SAS

DBailey — Thu, 01 Aug 2013 14:47:36 GMT

I could see it working this way:

proc sql;

create table work.base as

select distinct

id,

surg_date,

. format=date9. as Prior_Tab_Date,

'' length=1 as Prior_Flag,

. format=date9. as Post_Tab_Date,

'' length=1 as Post_Flag

from

work.have;

update base as t1

set

prior_tab_date = (select max(tablet) from work.have where id=t1.id and tablet < t1.surg_date),

post_tab_date=(select min(tablet) from work.have where id=t1.id and tablet >= t1.surg_date);

/*if you really need the flags*/

update base

set

prior_flag=case when prior_tab_date is not null then 'yes' end,

post_flag = case when post_tab_date is not null then 'yes 'end;

quit;

Re: SORT AND FLAG IN SAS

robertrao — Thu, 01 Aug 2013 14:49:07 GMT

Hi,

Thanks for the quick reply.

Could you show in SImple Datasteps. I am not good in SQl part

Thanks

Re: SORT AND FLAG IN SAS

Astounding — Thu, 01 Aug 2013 16:28:45 GMT

Assuming that your dates are actual SAS dates and not character strings, and that your data are pre-sorted, and that SURG_DATE is constant for each ID:

data want;

set have;

by id tablet;

if first.id then do;

prior_tab=.;

post_tab=.;

end;

retain prior_tab post_tab;

if (. < tablet < surg_date) then prior_tab = tablet;

if (. < surg_date <= tablet) and (post_tab=.) then post_tab=tablet;

if last.id;

if prior_tab > . then prior_flag='yes';

if post_tab > . then post_flag='yes';

run;

See if this gives you what you want.

Good luck.

Re: SORT AND FLAG IN SAS

robertrao — Thu, 01 Aug 2013 19:23:37 GMT

Thanks so very much.

I have a couple more questions regarding the code:

why do we specifically say post tab has to be missing also??

and (post_tab=.) then post_tab=tablet;

secondly,

in the IF conditions why are we first comparing the dates with the missing ?????

if(. <tablet)

if (. <surg_date);

Thanks

Re: SORT AND FLAG IN SAS

Astounding — Thu, 01 Aug 2013 19:38:39 GMT

For the first question, remember that you want the closest TABLET as the POST_TAB value. If there are 5 TABLET values that fall after the SURG_DATE, you want to use the first of those 5 to populate POST_TAB. Then ignore the remaining 4. That condition (POST_TAB=.) allows the program to ignore the remaining 4.

For the second question, it's possible that checking for (. < TABLET) is overkill and that you will never have missing values for TABLET. But the same cannot be said for SURG_DATE. Your WANT picture for ID=600 shows what should happen there ... POST_TAB and PRIOR_TAB should remain missing even though TABLET has a non-missing value. So ID=600 illustrates why you need to check for (. < SURG_DATE) to avoid assigning POST_TAB when SURG_DATE is missing.

Re: SORT AND FLAG IN SAS

robertrao — Wed, 04 Sep 2013 16:13:00 GMT

Hi,

I need one more clarification on this code.

if the dates are sas date-times instead of the dates?????does the same code hold good???

Also in the code suggested we dint tell its a date while comparing the two dates?How does SAS know its a date comparision???

Thanks

Re: SORT AND FLAG IN SAS

Astounding — Wed, 04 Sep 2013 16:48:46 GMT

If you have date-times instead of dates, the same code should be fine. You might want to think about what should happen if two date-times fall on the same day, but at different times. That would be the only possible change and it represents first a thought process and only later a possible change to the code.

Regarding the comparison, SAS doesn't know that the variables contain dates. SAS makes a comparison of one numeric value to another numeric value. The logic ... does it make sense to perform such a comparison when the numeric variables contain dates ... is up to the programmer to verify.

Re: SORT AND FLAG IN SAS

robertrao — Wed, 04 Sep 2013 17:03:57 GMT

Hi,

Thanks for the reply.

"if two date-times fall on the same day, but at different times" then we need to take it as post surgery ie if tablet and the surgery were on the same day but at diffrent timings then we want to consider the tablet taken as post surgery

Thanks

Re: SORT AND FLAG IN SAS

Astounding — Wed, 04 Sep 2013 17:43:02 GMT

Just to confirm ...

If the tablet was taken on the same day as the surgery, but was taken before the surgery took place, then we should consider the tablet as taken post-surgery?

Re: SORT AND FLAG IN SAS

robertrao — Wed, 04 Sep 2013 17:52:32 GMT

Yes sir,

Thats right......

After you clarify to me the question i asked, i am also curious to learn what needs to be done if we want to mark it as prior when the tablet is taken on the same day but prior surgery???????

Thanks

Re: SORT AND FLAG IN SAS

robertrao — Wed, 04 Sep 2013 18:00:44 GMT

I think using datepart in the following would be suitable to use in situations where tablet taken and surgery on the same day with different times....(BUT WE WANT TO MARK IT AS POST)????

if (. < datepart(tablet) < datepart(surg_date)) then prior_tab = tablet;

if (. < datepart(surg_date) <= datepart(tablet)) and (post_tab=.) then post_tab=tablet;

OTHERWISE THE FOLLOWING NUMERIC COMPARISION WILL DO

if (. < tablet < surg_date) then prior_tab = tablet

if (. < surg_date <= tablet) and (post_tab=.) then post_tab=tablet

AM I RIGHT???

Thanks

Re: SORT AND FLAG IN SAS

Astounding — Wed, 04 Sep 2013 21:18:04 GMT

Exactly right. Good job.

Re: SORT AND FLAG IN SAS

robertrao — Wed, 04 Sep 2013 21:40:48 GMT

Thanks so much