BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Fable
Fluorite | Level 6

Say I have a dataset of laboratory records for a set of patients (ordered by ID and date from earliest to latest) with this format, I want to remove all the red colored labs and keep all the black colored ones.

 

Basically, I want to only keep labs for a specific patient (labs from different patients should not be compared against each other) that are done >30 days from the previous lab, while not counting the removed labs when evaluating the next lab (i.e. if a lab is marked in red I want to ignore it when evaluating if the next lab is within 30 days and should thus be removed).

 

You can see for patient 1111, their 3rd lab is within 30 days of their 2nd lab, but because the 2nd lab is already marked for removal the 2nd lab does not count and thus the 3rd lab should be kept.

 

Does any one have any advice or suggestions for how this could be accomplished in SAS?

 

Patient_IDLab_Date
1111Jan 1 2023
1111Jan 15 2023
1111Feb 3 2023
1111Feb 16 2023
2222Jan 2 2023
2222Jan 20 2023
2222Feb 8 2023
2222Feb 10 2023
2222Feb 12 2023
3333Jan 15 2023
3333Feb 5 2023
3333Feb 18 2023
1 ACCEPTED SOLUTION

Accepted Solutions
FreelanceReinh
Jade | Level 19

Hello @Fable and welcome to the SAS Support Communities!

 

Try this:

data have;
input Patient_ID Lab_Date anydtdte11.;
format Lab_Date yymmdd10.;
cards;
1111 Jan 1 2023
1111 Jan 15 2023
1111 Feb 3 2023
1111 Feb 16 2023
2222 Jan 2 2023
2222 Jan 20 2023
2222 Feb 8 2023
2222 Feb 10 2023
2222 Feb 12 2023
3333 Jan 15 2023
3333 Feb 5 2023
3333 Feb 18 2023
;

data want(drop=pivot_date);
set have;
by Patient_ID;
if first.Patient_ID then pivot_date=Lab_Date;
else if Lab_Date-pivot_date<=30 then delete;
else pivot_date=Lab_Date;
retain pivot_date;
run;

View solution in original post

3 REPLIES 3
FreelanceReinh
Jade | Level 19

Hello @Fable and welcome to the SAS Support Communities!

 

Try this:

data have;
input Patient_ID Lab_Date anydtdte11.;
format Lab_Date yymmdd10.;
cards;
1111 Jan 1 2023
1111 Jan 15 2023
1111 Feb 3 2023
1111 Feb 16 2023
2222 Jan 2 2023
2222 Jan 20 2023
2222 Feb 8 2023
2222 Feb 10 2023
2222 Feb 12 2023
3333 Jan 15 2023
3333 Feb 5 2023
3333 Feb 18 2023
;

data want(drop=pivot_date);
set have;
by Patient_ID;
if first.Patient_ID then pivot_date=Lab_Date;
else if Lab_Date-pivot_date<=30 then delete;
else pivot_date=Lab_Date;
retain pivot_date;
run;
mkeintz
PROC Star

This is a good example for using a "subsetting if" and the lag function:

 

data have;
  input Patient_ID Lab_Date anydtdte11.;
  format Lab_Date yymmdd10.;
cards;
1111 Jan 1 2023
1111 Jan 15 2023
1111 Feb 3 2023
1111 Feb 16 2023
2222 Jan 2 2023
2222 Jan 20 2023
2222 Feb 8 2023
2222 Feb 10 2023
2222 Feb 12 2023
3333 Jan 15 2023
3333 Feb 5 2023
3333 Feb 18 2023
;

data want (drop=_:);
  set have;
  retain _refdate '01jan1900'd;
  if patient_id^=lag(patient_id) or lab_date-_refdate >30;
  _refdate=lab_date;
run;

It doesn't matter what value you use as the intial value of _refdate, because it will be immediately overwritten with the value of lab_date in the first observation.  In fact, any non-missing number could be the initial value - I just decided to use a date-literal to remind the reader that it is being managed as a date variable.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 941 views
  • 5 likes
  • 4 in conversation