Define date format on the basis of given date in a txt file

Question

I have been provided dates/datetimes in .txt file

For example:

20180422
02122018
2018/04/22
22/04/2018
04-02-2018
2018-04-20 13:05:56

Now, first I do import above .txt file, so it will be in numeric format in sas dataset. For each date I need to automatically define date_format.

Format examples:

yyyymmdd
mmddyyyy
yyyy/mm/dd
dd/mm/yyyy
dd-mm-yyyy
yyyy-mm-dd hh:mm:ss

How do I define the format based on the given date?

So it needs to define the date format judging from the date itself? What if it's 1 January? How would the program know whether it's the day or the month? — Steven, May 01 '18 at 07:01
Please add to the question an example of your .txt file/ imported data and the output ? I will put an initial answer to see if this answers your question. — momo1644, May 01 '18 at 08:49
Please read [Under what circumstances may I add “urgent” or other similar phrases to my question, in order to obtain faster answers?](//meta.stackoverflow.com/q/326569) - the summary is that this is not an ideal way to address volunteers, and is probably counterproductive to obtaining answers. Please refrain from adding this to your questions. — halfer, May 01 '18 at 09:14
Is the question how to convert the value to a date value? Or is it how to identify the format (pattern) that is being used for each different observation? — Tom, May 01 '18 at 12:49

momo1644 · Accepted Answer · 2018-05-01T11:03:27.513

In my first step I saved the dates as String then used input(a,anydtdte20.) in the following step to convert them to SAS dates and input(a,ymddttm24.) for datetime; this way all the dates with '/' and'-' will be read as SAS dates.

Please note that I am using the locale for DMY and in the case of datetime I extract only the date part.

options DATESTYLE=DMY;
data have;
length a $ 23 ;
input a $;
datalines;
20180422
12022018
2018/04/22
22/04/2018
04-02-2018
2018-04-20T13:05:56
;
run;

data want;
set have;
format date_a date9.;
date_a=input(a,anydtdte20.);
Year_a=year(date_a);
month_a=month(date_a);
day_a=day(date_a);
run;

Output (Want data set):

a=20180422 date_a=22APR2018 Year_a=2018 month_a=4 day_a=22
a=12022018 date_a=12FEB2018 Year_a=2018 month_a=2 day_a=12
a=2018/04/22 date_a=22APR2018 Year_a=2018 month_a=4 day_a=22
a=22/04/2018 date_a=22APR2018 Year_a=2018 month_a=4 day_a=22
a=04-02-2018 date_a=04FEB2018 Year_a=2018 month_a=2 day_a=4
a=2018-04-20T13:05:56 date_a=20APR2018 Year_a=2018 month_a=4 day_a=20

You can find more dynamic date & datetime informats in this SAS Post.

To take this one step further you can create extra columns/variables for each format you want, datetime handling added here:

proc sql;
create table dates as 
select
a as Date_String ,
date_a as SAS_Date9 format=date9. ,
date_a as SAS_YYMMDDN8 format=YYMMDDN8. ,
date_a as SAS_DDMMYYN8 format=DDMMYYN8. ,
date_a as SAS_YYMMDDS10 format=YYMMDDS10. ,
date_a as SAS_DDMMYYS10 format=DDMMYYS10. ,
case when LENGTH(a) > 10 then input(a,ymddttm24.) else . end as SAS_Datetime21 format=datetime21. ,
case when LENGTH(a) > 10 then timepart(input(a,ymddttm24.)) else . end as SAS_Time format=time8. 
from work.want;
quit;

Output:

SAS_Date9=22APR2018 SAS_YYMMDDN8=20180422 SAS_DDMMYYN8=22042018 SAS_YYMMDDS10=2018/04/22
SAS_DDMMYYS10=22/04/2018 SAS_Datetime21=. SAS_Time=.
SAS_Date9=12FEB2018 SAS_YYMMDDN8=20180212 SAS_DDMMYYN8=12022018 SAS_YYMMDDS10=2018/02/12
SAS_DDMMYYS10=12/02/2018 SAS_Datetime21=. SAS_Time=.
SAS_Date9=22APR2018 SAS_YYMMDDN8=20180422 SAS_DDMMYYN8=22042018 SAS_YYMMDDS10=2018/04/22
SAS_DDMMYYS10=22/04/2018 SAS_Datetime21=. SAS_Time=.
SAS_Date9=22APR2018 SAS_YYMMDDN8=20180422 SAS_DDMMYYN8=22042018 SAS_YYMMDDS10=2018/04/22
SAS_DDMMYYS10=22/04/2018 SAS_Datetime21=. SAS_Time=.
SAS_Date9=04FEB2018 SAS_YYMMDDN8=20180204 SAS_DDMMYYN8=04022018 SAS_YYMMDDS10=2018/02/04
SAS_DDMMYYS10=04/02/2018 SAS_Datetime21=. SAS_Time=.
SAS_Date9=20APR2018 SAS_YYMMDDN8=20180420 SAS_DDMMYYN8=20042018 SAS_YYMMDDS10=2018/04/20
SAS_DDMMYYS10=20/04/2018 SAS_Datetime21=20APR2018:13:05:56 SAS_Time=13:05:56

Thanks a lot, ok, now I can transpose variable right, actually in .txt file i have date for example, 20180420 and final output I need yyyymmdd — user1645514, May 01 '18 at 11:46
There are around 100 columns in .txt file but only thing I need your help in to define date format basis on given date. Say out of 100 column there 5-6 columns with date field, so each I need to define date format — user1645514, May 01 '18 at 12:02

Define date format on the basis of given date in a txt file

1 Answers1