Keep only value and date that occurred before or at last visit date

[tan-wongv]

I have a dataset contains DMRN, last visit date, the presence of UI, and recorded date of UI

DMRN	last_visit	UI	recorded_time
31	26AUG2021	0	06APR2018
31	26AUG2021	0	16JAN2020
31	26AUG2021	1	4MAY2021
31	26AUG2021	1	26MAY2022
33	24MAY2022	0	02MAR2020
33	24MAY2022	0	24MAY2022
35	01DEC2014	0	25MAR2013
35	01DEC2014	1	05JAN2015

I would like to keep only UI value and recorded_time that occurred before or equal to last_visit date for each DMRN. Could you please help me with the code?The outcome would look like this:

DMRN	last_visit	UI	recorded_time
31	26AUG2021	0	06APR2018
31	26AUG2021	0	16JAN2020
31	26AUG2021	1	4MAY2021
33	24MAY2022	0	02MAR2020
33	24MAY2022	0	24MAY2022
35	01DEC2014	0	25MAR2013

[Tom · Accepted solution]

Seems simple enough.  First let's convert your existing (HAVE) data listing and expected (EXPECT) data listings into datasets.  Then you just need to select the observations where recorded_time is less than or equal to last_visit.

data have;
  input DMRN $ last_visit :date. UI recorded_time :date.;
  format last_visit recorded_time date9.;
cards;
31 26AUG2021 0 06APR2018
31 26AUG2021 0 16JAN2020
31 26AUG2021 1 4MAY2021
31 26AUG2021 1 26MAY2022
33 24MAY2022 0 02MAR2020
33 24MAY2022 0 24MAY2022
35 01DEC2014 0 25MAR2013
35 01DEC2014 1 05JAN2015
;

data expect;
  input DMRN $ last_visit :date. UI recorded_time :date.;
  format last_visit recorded_time date9.;
cards;
31 26AUG2021 0 06APR2018
31 26AUG2021 0 16JAN2020
31 26AUG2021 1 4MAY2021
33 24MAY2022 0 02MAR2020
33 24MAY2022 0 24MAY2022
35 01DEC2014 0 25MAR2013
;

data want ;
  set have;
  where recorded_time <= last_visit;
run;

proc compare data=want compare=expect;
run;

[tan-wongv]

Thank you so much for your response!  I tried this code but the outcome just showed the observation where recorded_time is less than last_vist. It did not include the recorded_time that is equal to last_visit. 

[Tom]

---

@tan-wongv wrote:

Thank you so much for your response!  I tried this code but the outcome just showed the observation where recorded_time is less than last_vist. It did not include the recorded_time that is equal to last_visit. 

---

That could only happen if the date values are not integers.  The DATE format will ignore fractions of a day so the values would print as being equal even though they would compare as not equal.

Try looking at the values for one of the cases where the values look to you like they are equal but did not get selected by the WHERE (or IF) test.  Display the values with out the format or with some other format such as BEST32. or HEX16.

How did you enter those values into your SAS dataset?

[tan-wongv]

Thank you so much for your guide!! I found the problem as you said. I fixed it and now your code works 🙂