BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
robertrao
Quartz | Level 8

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

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

13 REPLIES 13
DBailey
Lapis Lazuli | Level 10

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;

robertrao
Quartz | Level 8

Hi,

Thanks for the quick reply.

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

Thanks

Astounding
PROC Star

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.

robertrao
Quartz | Level 8

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

Astounding
PROC Star

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.


robertrao
Quartz | Level 8

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

Astounding
PROC Star

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.

robertrao
Quartz | Level 8

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

Astounding
PROC Star

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?

robertrao
Quartz | Level 8

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

robertrao
Quartz | Level 8

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

Astounding
PROC Star

Exactly right.  Good job.

robertrao
Quartz | Level 8

Thanks so much

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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