How do I compare dates of different formats?

[elli444]

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;

[ed_sas_member]

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; 

[elli444]

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?

[Kurt_Bremser]

---

@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?

[elli444]

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

[Kurt_Bremser]

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.

[Kurt_Bremser]

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

[Astounding]

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?