If datetime is empty then ... format comparative datetime

[fjsouz]

I am trying to create a variable that is the most recent date.
In this case, I want it to use the registration date column if the update date is blank, if the update date is more recent than the registration I want the update date to be used.

data new;
set old;
if update_date = . then last_date = registration_date;
else if update_date GE registration_date then last_date = update_date;
else if update_date LT registration_date then last_date = registration_date;
last_date1 = datepart(input(last_date, datetime8.));
last_date1 = put(last_date1,ddmmyy10.);
run;

Then I will compare if the date_abta is more recent than the last date (this is another problem because the date_abta field uses another type of datetime variable)

result of my code

client_code	registration_date	update_date	last_date	last_date1	date_abta
1	31AUG81:00:00:00	11JAN00:00:00:00	14620		26FEV2020:00:00:00:000000
2	31AUG81:00:00:00		683683200
3	05NOV82:00:00:00	23SEPT19:10:00:00	19773
4	02OCT81:00:00:00	05JUN19:01:00:00	22140		04AUG2020:00:00:00:000000
5	06OCT81:00:00:00	09JUN20:11:39:00	22075		09JUN2020:00:00:00:000000

Expected

client_code	registration_date	update_date	last_date	last_date1	date_abta
1	31AUG81:00:00:00	11JAN00:00:00	11JAN00		26FEV2020:00:00:00:000000
2	31AUG81:00:00:00		31AUG81
3	05NOV82:00:00:00	23SEPT19:00:00:00	23SEPT19
4	02OCT81:00:00:00	05JUN19:01:00:00	05JUN19		04AUG2020:00:00:00:000000
5	06OCT81:00:00:00	09JUN20:11:39:00	09JUN20		09JUN2020:00:00:00:000000

I don't know which format to use and how to compare dates with different formats.

[jimbarbour]

Thank you for all the information that you have provided.  

One more thing, though:  Can you provide some sample data?  I think this can be accomplished, but I'd like to see actual data (or as close as it can be).  If you post it as DATALINES in a SAS program, that's even better.

Jim

[jimbarbour · Accepted solution]

Without test data, I can only do so much, but here's a quick effort.  

First, the results:

Then the code, below.

How does this all look?

Jim

DATA	Old;
*	DROP	_:;

	INFILE	DATALINES	DSD	DLM='09'X	MISSOVER;

	LENGTH
		client_code				$1
		_registration_date		$16
		_update_date			$17
		_date_abta				$25
		;

	INPUT
		client_code				$
		_registration_date		$
		_update_date			$
		_date_abta				$
		;

DATALINES4;
1	31AUG81:00:00:00	11JAN00:00:00:00	26FEB2020:00:00:00:000000
2	31AUG81:00:00:00	
3	05NOV82:00:00:00	23SEPT19:10:00:00	 
4	02OCT81:00:00:00	05JUN19:01:00:00	04AUG2020:00:00:00:000000
5	06OCT81:00:00:00	09JUN20:11:39:00	09JUN2020:00:00:00:000000
;;;;
RUN;

**------------------------------------------------------------------------------**;

data new;
	DROP	_:;

	set old;

	FORMAT	
		client_code			$1.
		registration_date	DATETIME20.
		update_date			DATETIME20.
		date_abta			DATETIME20.
		last_date			DATETIME20.
		last_date1			DATE9.
		;

	registration_date	=	INPUT(_registration_date,	ANYDTDTM16.);
	update_date			=	INPUT(_update_date, 		ANYDTDTM16.);
	date_abta			=	INPUT(_date_abta, 			ANYDTDTM16.);

	if update_date = . then last_date = registration_date;
	else if update_date GE registration_date then last_date = update_date;
	else if update_date LT registration_date then last_date = registration_date;
	last_date1 = datepart(last_date);
run;

[ballardw]

Your data step code has two issues that are problematic:

last_date1 = datepart(input(last_date, datetime8.));
last_date1 = put(last_date1,ddmmyy10.);

The question is do you want Last_date1 to be a date value or character? And what should it actually look like? Since Last_date is, or from the code shown, be mostly a datetime value when you do something like

input(last_date,datetime8.)

I expect an error because INPUT requires a CHARACTER value for conversion the datetime informat is not going to like the implicit numeric to character conversion result

In fact when I run some code using that input this is what I see in the log.

2    data junk;
3       last_date = "31Aug81:00:00:00"dt;
4       put last_date=;
5       ex = input(last_date,datetime8.);
                             ----------
                             499
ERROR 499-185: Width specified for informat DATETIME is invalid.

6       put ex=;
7    run;

NOTE: Numeric values have been converted to character values at the places given by:
      (Line):(Column).
      5:15
NOTE: The SAS System stopped processing this step because of errors.
WA

If you want to use the the date part of the datetime just do so.

Either, if you want a date value (recommended)

last_date1= datepart(last_date);
format last_date1 ddmmyy10.;

Or if you want a character value:

Last_date1 = put(datepart(last_date), ddmmyy10.);

However the shown "results" don't match your code at all. The 5 digit values of last_date would be "dates" not "datetimes" which are what your pictures of registration_date and update_date imply. So one expects that your shown code is not quite exactly what you ran.

[Sajid01]

Hello @fjsouz 
The basic fact to remember is the "datatime" value represents the number of seconds since  the midnight  of 1st January 1960 and date value represents number of days  since 1st  January 1960
(https://go.documentation.sas.com/doc/en/lrcon/9.4/p1wj0wt2ebe2a0n1lv4lem9hdc0v.htm#n1hq98rwxstsjhn1h... ).
Depending upon the  business need, the appropriate form can be used.
With your question in mind the code by @jimbarbour should server your purpose.