Date format informat

[art_srap]

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.

[PeterClemmensen]

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

[art_srap]

'um' and 'umk'

[PaigeMiller]

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.

[art_srap]

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.

[PaigeMiller]

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

This assumes you have a character variable named DATECHAR

[art_srap]

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

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

[PaigeMiller]

---

@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)

[Tom]

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

[art_srap]

It's valid,thanks a lot.

[sahoositaram555]

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.);

[Tom]

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.

[sahoositaram555]

Thanks @Tom.

[art_srap]

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
;