Help using Base SAS procedures

SORT AND FLAG IN SAS

Accepted Solution Solved
Reply
Super Contributor
Posts: 1,041
Accepted Solution

SORT AND FLAG IN SAS

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


Accepted Solutions
Solution
‎08-01-2013 12:28 PM
Super User
Posts: 5,498

Re: SORT AND FLAG IN SAS

Posted in reply to robertrao

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.

View solution in original post


All Replies
Super Contributor
Posts: 578

Re: SORT AND FLAG IN SAS

Posted in reply to robertrao

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;

Super Contributor
Posts: 1,041

Re: SORT AND FLAG IN SAS

Hi,

Thanks for the quick reply.

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

Thanks

Solution
‎08-01-2013 12:28 PM
Super User
Posts: 5,498

Re: SORT AND FLAG IN SAS

Posted in reply to robertrao

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.

Super Contributor
Posts: 1,041

Re: SORT AND FLAG IN SAS

Posted in reply to Astounding

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

Super User
Posts: 5,498

Re: SORT AND FLAG IN SAS

Posted in reply to robertrao

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.


Super Contributor
Posts: 1,041

Re: SORT AND FLAG IN SAS

Posted in reply to Astounding

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

Super User
Posts: 5,498

Re: SORT AND FLAG IN SAS

Posted in reply to robertrao

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.

Super Contributor
Posts: 1,041

Re: SORT AND FLAG IN SAS

Posted in reply to robertrao

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

Super User
Posts: 5,498

Re: SORT AND FLAG IN SAS

Posted in reply to robertrao

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?

Super Contributor
Posts: 1,041

Re: SORT AND FLAG IN SAS

Posted in reply to Astounding

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

Super Contributor
Posts: 1,041

Re: SORT AND FLAG IN SAS

Posted in reply to Astounding

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

Super User
Posts: 5,498

Re: SORT AND FLAG IN SAS

Posted in reply to robertrao

Exactly right.  Good job.

Super Contributor
Posts: 1,041

Re: SORT AND FLAG IN SAS

Posted in reply to Astounding

Thanks so much

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 13 replies
  • 521 views
  • 4 likes
  • 3 in conversation