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
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.
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;
Hi,
Thanks for the quick reply.
Could you show in SImple Datasteps. I am not good in SQl part
Thanks
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.
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
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.
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
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.
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
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?
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
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
Exactly right. Good job.
Thanks so much
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.