topic Re: If datetime is empty then ... format comparative datetime in SAS Programming

If datetime is empty then ... format comparative datetime

fjsouz — Wed, 26 May 2021 00:36:13 GMT

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.

Re: If datetime is empty then ... format comparative datetime

jimbarbour — Wed, 26 May 2021 00:46:05 GMT

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

Re: If datetime is empty then ... format comparative datetime

jimbarbour — Wed, 26 May 2021 01:35:42 GMT

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;

Re: If datetime is empty then ... format comparative datetime

ballardw — Wed, 26 May 2021 09:40:44 GMT

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.

Re: If datetime is empty then ... format comparative datetime

Sajid01 — Wed, 26 May 2021 13:51:52 GMT

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#n1hq98rwxstsjhn1hrf5tdyuzsrx ).
Depending upon the business need, the appropriate form can be used.
With your question in mind the code by @jimbarbour should server your purpose.