BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
harcluna
Calcite | Level 5

Hello,

 

I have about a 100 dates that I'm trying to format in a specific way i.e. Month space day, space year. So for 3july1965, I want to make it July 3, 1965. For 20121jul I want to make it July 1, 2012 and for 02112007 I want to make it July 11, 2007. I've only included these 3 as the rest are just repetition of this theme.

 

Any help is much appreciated

 

 

data dates;

input #1 @1 one $1-13
#2 @1 two $1-12
#3 @1 three $1-12;
datalines;
3july1965
20121Jul
07112007
;

proc print data=dates;
run;

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

@harcluna

You normally get answers quicker and better suited to your problem if you provide representative sample data via a working SAS data step covering all your cases. We can't know what you're actually dealing with.

 

Dates in SAS

SAS stores dates as the count of days since 1/1/1960 as a simple number in a numeric variable. You then can apply a SAS Date FORMAT to make this number human readable. Such a format is only for display and it doesn't change the internally stored value (the count of days). In your case the format is worddate.

You use SAS date INFORMATS to instruct SAS how to interprete a string representing a date so SAS can convert this string into a SAS Date value (the count of days).

 

The problem you're dealing with is that your date strings are so varying using multiple patterns for representing a date. There isn't a one fits all SAS INFORMAT to deal with all these patterns. You need to write code which can try multiple Informats and then stops once one of these Informats has been successful (=text string converted resulting in a non-missing numeric value).

You can find the list of available SAS Date Informats here:

http://support.sas.com/documentation/cdl/en/leforinforref/64790/HTML/default/viewer.htm#n0verk17pchh...

 

What complicates things: There is no Informat for a date string pattern of yyyydmon like 20121Jul 

For such cases you need either to implement your own informat or you need to add some additional logic in your code which transforms the string to something for which you've got an informat (i.e. recombine the string using substring() functions and then concatenate the substrings the way you need them).

The code below uses a combination of above. It creates a custom informat using a Regular Expression which recombines the input string and then applies the DATE9. informat to this recombined string. That's only one way of doing things and if you're not familiar with Regular Expressions then you probably want to implement such logic as an additional block in your data step.

 

Here you go:

proc format;
   /* convert date string to a pattern for which there is a SAS Date Informat */
   invalue yyyyddmon (default=9)
    's/^\s*(\d{4})(\d{1,2}[a-z]{3})\s*$/\2\1/i' (regexpe) = [date9.] 
    other=_same_; 
run;

options datestyle=mdy;
data sample;
  input inDateString :$20.;
  format outSASDateValue worddate.;
  if not missing(inDateString) then 
    do;;
      /* try: read the string and convert to SAS Date value using different INformats */
      do tryInFMT='anydtdte.','mmddyy10.','yyyyddmon.';
        outSASDateValue=inputn(inDateString,tryInFMT);
        /* check: stop trying if conversion successful */
        if not missing(outSASDateValue) then leave;
      end;
    end;
  datalines;
3july1965
20121Jul
201201Jul
07112007
07/11/2007
;

proc print;
run;

 

 

 

View solution in original post

7 REPLIES 7
Astounding
PROC Star

You have two issues here.  First is how to obtain a valid SAS date value from your incoming text.  Second is how to display that value in the format that you desire.  Untested, but should handle both issues:

 

data dates;

input dateval anydtdte9.;

format dateval mmddyyb10.;
datalines;
3july1965
20121Jul
07112007
;

proc print;

run;

 

The ANYDTDTE informat reads most date and datetime text strings, and converts them to the equivalent SAS date value.  The MMDDYYB10. format displays dates in 10 characters, month then day then year, with B=Blanks as the separator.

 

There may be some text strings that ANYDTDTE can't figure out.  And it may have to make a decision if it sees a string like 07112007 (which is the month and which is the day).  You'll have to test it and see what you get.

mkeintz
PROC Star

This is a perfect situation to use the PICTURE statement to make a user-defined format, instead of the value statement.  That's because the picture statement honors the often neglected datatype=date parameter, which in turn supports use of "format directives" (the "%" codes below):

 

 

proc format;
  picture myfmt (min=18)
    low-high = '%B %d, %Y ' (datatype=date) ;
run;

data _null_;
  do d='15jan2014'd to '01jan2015'd by 30;
    put d=date9.   d=myfmt.;
  end;
run;

 

In the sas help materials search for documentation on the picture statement.  This capability is not present in the value statement.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
ballardw
Super User

@mkeintz wrote:

 

 

proc format;
  picture myfmt (min=18)
    low-high = '%B %d, %Y ' (datatype=date) ;
run;

data _null_;
  do d='15jan2014'd to '01jan2015'd by 30;
    put d=date9.   d=myfmt.;
  end;
run;

 

 


SAS supplied format WORDDATE.

data _null_;
  do d='15jan2014'd to '01jan2015'd by 30;
    put d=date9.   d=worddate.;
  end;
run;
harcluna
Calcite | Level 5

Thank you for your help, but I still can't get my data to look like I want. The code below shows how I'd like it to end up, but I'm just having trouble with the intermediate steps.

 

data testdate;
length chardate $8;
infile datalines;
input chardate $;
date = input(chardate,yymmdd8.);
return;
datalines;
20170701
;
run;

ods listing;
proc print data=testdate;
title '1) Internally stored values';
run;

proc print data=testdate;
title '2) Using SAS format with dates';
format date worddate.;
run;

Patrick
Opal | Level 21

@harcluna

Running the code you've posted looks good to me.

Capture.JPG

 

What is not working as you want? Please explain in detail.

harcluna
Calcite | Level 5

Hi Patrick,

Thank you for your interest. The problem is I have a lot of dates in a format different from that in the code e.g.
3july1965
20121Jul
07112007

What I would like to do is convert them to this format:
July, 3 1965
July, 21 2012
July, 11 2007

The date format I used in the code
20170701 converted to July, 1 2017, but I can't convert the other formats so the code will run properly (I have a lot of dates so I don't want to change them all by hand).

 

Patrick
Opal | Level 21

@harcluna

You normally get answers quicker and better suited to your problem if you provide representative sample data via a working SAS data step covering all your cases. We can't know what you're actually dealing with.

 

Dates in SAS

SAS stores dates as the count of days since 1/1/1960 as a simple number in a numeric variable. You then can apply a SAS Date FORMAT to make this number human readable. Such a format is only for display and it doesn't change the internally stored value (the count of days). In your case the format is worddate.

You use SAS date INFORMATS to instruct SAS how to interprete a string representing a date so SAS can convert this string into a SAS Date value (the count of days).

 

The problem you're dealing with is that your date strings are so varying using multiple patterns for representing a date. There isn't a one fits all SAS INFORMAT to deal with all these patterns. You need to write code which can try multiple Informats and then stops once one of these Informats has been successful (=text string converted resulting in a non-missing numeric value).

You can find the list of available SAS Date Informats here:

http://support.sas.com/documentation/cdl/en/leforinforref/64790/HTML/default/viewer.htm#n0verk17pchh...

 

What complicates things: There is no Informat for a date string pattern of yyyydmon like 20121Jul 

For such cases you need either to implement your own informat or you need to add some additional logic in your code which transforms the string to something for which you've got an informat (i.e. recombine the string using substring() functions and then concatenate the substrings the way you need them).

The code below uses a combination of above. It creates a custom informat using a Regular Expression which recombines the input string and then applies the DATE9. informat to this recombined string. That's only one way of doing things and if you're not familiar with Regular Expressions then you probably want to implement such logic as an additional block in your data step.

 

Here you go:

proc format;
   /* convert date string to a pattern for which there is a SAS Date Informat */
   invalue yyyyddmon (default=9)
    's/^\s*(\d{4})(\d{1,2}[a-z]{3})\s*$/\2\1/i' (regexpe) = [date9.] 
    other=_same_; 
run;

options datestyle=mdy;
data sample;
  input inDateString :$20.;
  format outSASDateValue worddate.;
  if not missing(inDateString) then 
    do;;
      /* try: read the string and convert to SAS Date value using different INformats */
      do tryInFMT='anydtdte.','mmddyy10.','yyyyddmon.';
        outSASDateValue=inputn(inDateString,tryInFMT);
        /* check: stop trying if conversion successful */
        if not missing(outSASDateValue) then leave;
      end;
    end;
  datalines;
3july1965
20121Jul
201201Jul
07112007
07/11/2007
;

proc print;
run;

 

 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 2727 views
  • 0 likes
  • 5 in conversation