BookmarkSubscribeRSS Feed
art_srap
Obsidian | Level 7

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.

 

13 REPLIES 13
PeterClemmensen
Tourmaline | Level 20

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

art_srap
Obsidian | Level 7

'um' and 'umk'

PaigeMiller
Diamond | Level 26

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.

 

 

--
Paige Miller
art_srap
Obsidian | Level 7

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
Diamond | Level 26
data want;
    set have;
    datechar = transtrn(upcase(datechar),'UMK','');
    datechar = transtrn(upcase(datechar),'UM','');
run;

This assumes you have a character variable named DATECHAR

--
Paige Miller
art_srap
Obsidian | Level 7

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

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

PaigeMiller
Diamond | Level 26

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

--
Paige Miller
Tom
Super User Tom
Super User

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
Obsidian | Level 7

It's valid,thanks a lot.

sahoositaram555
Pyrite | Level 9
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
Super User Tom
Super User
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
Pyrite | Level 9
Thanks @Tom.
art_srap
Obsidian | Level 7

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
;

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 13 replies
  • 1518 views
  • 2 likes
  • 5 in conversation