topic Re: Date formatting in SAS Programming

Date formatting

harcluna — Fri, 22 Sep 2017 11:31:11 GMT

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;

Re: Date formatting

Astounding — Fri, 22 Sep 2017 11:46:00 GMT

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.

Re: Date formatting

mkeintz — Fri, 22 Sep 2017 14:36:59 GMT

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.

Re: Date formatting

ballardw — Fri, 22 Sep 2017 16:58:39 GMT

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

Re: Date formatting

harcluna — Sat, 23 Sep 2017 04:22:30 GMT

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;

Re: Date formatting

Patrick — Sat, 23 Sep 2017 04:46:07 GMT

@harcluna

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

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

Re: Date formatting

harcluna — Sat, 23 Sep 2017 09:43:17 GMT

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

Re: Date formatting

Patrick — Sun, 24 Sep 2017 07:02:01 GMT

@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#n0verk17pchh4vn1akrrv0b5w3r0.htm

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;