topic Re: Date format informat in SAS Programming

Date format informat

art_srap — Sun, 05 Apr 2020 10:13:27 GMT

Hi SAS experts,

I have a data

21APR2004
18JUL1981
umfeb2007
UMUMK1968
umumkumk

How can I create output like this?

2004-04-21

1981-07-18

2007-02

1968

Thanks for any help.

Re: Date format informat

PeterClemmensen — Sun, 05 Apr 2020 10:51:13 GMT

Is the 'invalid date text' always 'um'?

Re: Date format informat

art_srap — Sun, 05 Apr 2020 11:01:06 GMT

'um' and 'umk'

Re: Date format informat

PaigeMiller — Sun, 05 Apr 2020 12:04:04 GMT

Is "um" always the indicator for a missing date, and "umk" the indicator for a missing month and missing year? Or can they be interchangeable?

The other problem you face is that the desired output, if it is to be a column of dates, can't accommodate actual dates like 2004-04-21, and year-month like 2007-02 and year only like 1968 in the same column. Now, if you want it as a column of text strings, then the problem is simple, but you don't make it clear if the output is a column of SAS dates (which are numeric) or if the output is text strings.

Re: Date format informat

art_srap — Sun, 05 Apr 2020 12:48:18 GMT

Thanks for the reply. It could be as a column of text strings if it's a way to get this output.
About "um" and "umk" yes, I have only this data in this task.

Re: Date format informat

PaigeMiller — Sun, 05 Apr 2020 13:17:07 GMT

data want;
    set have;
    datechar = transtrn(upcase(datechar),'UMK','');
    datechar = transtrn(upcase(datechar),'UM','');
run;

This assumes you have a character variable named DATECHAR

Re: Date format informat

art_srap — Sun, 05 Apr 2020 14:12:50 GMT

Thank you. I got this output. https://prnt.sc/rtgk4a

There's no way to get like this? https://prnt.sc/rtgn9n

Re: Date format informat

Tom — Sun, 05 Apr 2020 14:24:07 GMT

Check if it is a valid date. If not check if making the first two characters 01 make it a date, otherwise read the last 4 characters as the year.

data test;
  input have $9. ;
  date=input(have,??date9.);
  if not missing(date) then want=put(date,yymmdd10.);
  else do;
    date=input(tranwrd(upcase(have),'UM','01'),??date9.);
    if not missing(date) then want=put(date,yymmd7.);
    else do;
      date=input(substr(have,6),??4.);
      if 1800 < date <= 9999 then want=put(date,4.);
    end;
  end;
  drop date;
cards;
21APR2004
18JUL1981
umfeb2007
UMUMK1968
umumkumk
;

Obs      have       want

 1     21APR2004    2004-04-21
 2     18JUL1981    1981-07-18
 3     umfeb2007    2007-02
 4     UMUMK1968    1968
 5     umumkumk

Re: Date format informat

art_srap — Sun, 05 Apr 2020 14:55:50 GMT

It's valid,thanks a lot.

Re: Date format informat

PaigeMiller — Sun, 05 Apr 2020 20:52:33 GMT

@art_srap wrote:

Thank you. I got this output. https://prnt.sc/rtgk4a

There's no way to get like this? https://prnt.sc/rtgn9n

Just a comment for your future reference. I'm not going to click on those links. Just copy and paste (as text) the output you get into your reply (click on the </> icon before doing the paste)

Re: Date format informat

sahoositaram555 — Sun, 05 Apr 2020 21:40:06 GMT

Hi @Tom, could you please specify the reason behind the logic.May i please have a reason for using 9999 as a upper cutoff?
if 1800 < date <= 9999 then want=put(date,4.);

Re: Date format informat

Tom — Sun, 05 Apr 2020 22:29:09 GMT

The ?? prevents invalid strings from generating errors. Invalid strings will result in missing values.

You can pick any reasonable year range limits you want. 9999 is the largest number that can by represented by 4 digits. Also the largest year that the date format can present. There was just another post today where someone was using from/to dates to represents time periods when data was current and they were using year of 9999 to represent values that are still actively being used.

Re: Date format informat

sahoositaram555 — Mon, 06 Apr 2020 13:47:53 GMT

Thanks @Tom.

Re: Date format informat

art_srap — Thu, 09 Apr 2020 19:12:35 GMT

Dear Tom,
Is it possible to achieve same result without tranwrd (without removing 'UM' or other letters)? I need to have a dynamic code.
I started to solve on this way , but it didn't work yet. Thanks in advance.

data task_1;
input date: $9. ;

day=substr(date,1,2);
month=substr(date,3,3);
year=substr(date,6,4);

if (day) then day1=day;
if (year) then year1=year;
month1=put(month,3.);
if (month) then month1=month;
newdate=cats(year1,month1,day1);

datalines;
21APR2004
18JUL1981
umfeb2007
UMUMK1968
umumkumk
;