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.
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;
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
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;
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.