topic Re: How do I compare dates of different formats? in SAS Programming

How do I compare dates of different formats?

elli444 — Sat, 21 Dec 2019 05:57:15 GMT

Hello I am trying to compare two dates that have two different formats and I am at a loss. My goal is to remove any observations from my dataset where the Death date (dthdate) is before the last date that a lab came in (LastEvent). Both variables are numeric.

The dthdate variable is in the format M_D_YYYY. Meaning, there aren't always leading zeroes before the month and day. One variable might be 4152017 (04/15/2017) and another might 1242017 (12/04/2017). I am not sure which format this is in SAS.

The LastEvent variable is in the form YYYYMM.

Any help y'all have to offer is much appreciated. Cheers!

Below is the code I've been playing with so far. I am trying to do this in a data step if possible.

data ssdmf_checked_export;
set ssdmf_checked;

dthdate_char = PUT(DTHDATE_File1, yymmdd10.);

LastEvent_char = PUT(LAST_EVENT_YM_File2, yymmdd10.);

if input(dthdate,mmddyy10.) < input(LastEvent,mmddyy10.);

run;

Re: How do I compare dates of different formats?

ed_sas_member — Sat, 21 Dec 2019 09:39:57 GMT

Hi @elli444

This should be the right syntax for LastEvent -> it will give the first day of the month (e.g. 201712 -> 01DEC2017).

However, what should be the dthdate for 1112017? 1JAN2017 or 11NOV2017?

Another example: 1242017 -> 4DEC2017 or 24JAN2017?

Is it your raw data or is it possible to import / retrieve them differently?

data ssdmf_checked;
	input dthdate LastEvent;
	datalines;
4152017 201712
1242017 201705
;
run;

data ssdmf_checked_export;
	set ssdmf_checked;
	format dthdate_n LastEvent_n date9.; 
	
	*dthdate_n = input(put(dthdate,z8.),??);
	LastEvent_n = input(put(LastEvent,z6.),yymmn6.);

	if dthdate_n < LastEvent_n then delete;
run;

Re: How do I compare dates of different formats?

Kurt_Bremser — Sat, 21 Dec 2019 15:08:46 GMT

These dates are useless. 1242017 could be 12/4/2017 or 1/24/2017.

Re: How do I compare dates of different formats?

Astounding — Sat, 21 Dec 2019 15:09:31 GMT

So you have a real problem here. Look at this date: 1242017

Why is it 12/04/217 ?

Couldn't it be 01/24/2017 ?

Do you have a way to determine that?

Re: How do I compare dates of different formats?

elli444 — Mon, 23 Dec 2019 18:30:35 GMT

This is very helpful. I think the dthDate is actually just missing the leading zero for the month. So that means _MDDYYYY. Do you know what format that appears as in SAS?

Re: How do I compare dates of different formats?

Kurt_Bremser — Mon, 23 Dec 2019 18:48:04 GMT

@elli444 wrote:

This is very helpful. I think the dthDate is actually just missing the leading zero for the month. So that means _MDDYYYY. Do you know what format that appears as in SAS?

Then your assumption in your first post that it is December 4 is wrong?

Re: How do I compare dates of different formats?

elli444 — Mon, 23 Dec 2019 18:50:22 GMT

Yes, I was mistaken. After looking through the dataset for several examples there are leading zeroes for day but not for month.

Re: How do I compare dates of different formats?

Kurt_Bremser — Mon, 23 Dec 2019 19:34:45 GMT

So, in order to get it right, you should add a leading zero to the string if length is 7, and then use mmddyy8. in the input.