topic Date Format troubles in SAS Programming

Date Format troubles

JediApprentice — Thu, 21 Dec 2017 19:48:47 GMT

I am attempting to change dates from a format of "January 2017" to 01/01/2017 and so on. I created a custom format below that is more of a quick fix. But I need to have a format be dynamic such that when 2018 comes around, it will apply to that as well.

data dummy2;
  infile datalines;
  length Date $14.;
  input Date 1-14;
  cards;
January 2017
February 2017
March 2017
April 2017
May 2017
June 2017
July 2017
August 2017
September 2017
October 2017
November 2017
December 2017
;;;;
run;


proc format;
  value $new_date 'January 2017'= '01/01/2017'
                  'February 2017'= '02/01/2017'
				  'March 2017'= '03/01/2017'
				  'April 2017'= '04/01/2017'
				  'May 2017'= '05/01/2017'
				  'June 2017'= '06/01/2017'
				  'July 2017'= '07/01/2017'
				  'August 2017'= '08/01/2017'
				  'September 2017'= '09/01/2017'
				  'October 2017'= '10/01/2017'
				  'November 2017'= '11/01/2017'
				  'December 2017'= '12/01/2017';
run;

data x;
  set dummy2;
  format Date $new_date.;
run;

Re: Date Format troubles

ChrisHemedinger — Thu, 21 Dec 2017 20:05:33 GMT

Probably a better way, but here's a one-liner.

realdate = input(catt (substr(date,1,3), scan(date,2,' ')),monyy7.);

Full test:

data dummy2;
  infile datalines;
  length Date $14.;
  input Date 1-14;
  cards;
January 2017
February 2017
March 2017
April 2017
May 2017
June 2017
July 2017
August 2017
September 2017
October 2017
November 2017
December 2017
;;;;
run;

data intoDates;
  set dummy2;
  length realdate 8;
  format realdate mmddyy10.;
  realdate = input(catt (substr(date,1,3), scan(date,2,' ')),monyy7.);
run;

Re: Date Format troubles

novinosrin — Thu, 21 Dec 2017 20:08:35 GMT

some fun to play with functions and not formats:

data dummy2;
infile datalines;
input Date $14.;
cards;
January 2017
February 2017
March 2017
April 2017
May 2017
June 2017
July 2017
August 2017
September 2017
October 2017
November 2017
December 2017
;;;;
run;

data want;
set dummy2;
new_date=put(input(cats('01',substr(date, 1,3), scan(Date,-1)),date9.),mmddyy10.);
run;

EDIT: @ChrisHemedinger My apologies if mine duplicates your solution. I honestly didn;t see it posted before i posted mine,

Re: Date Format troubles

ChrisHemedinger — Thu, 21 Dec 2017 20:09:54 GMT

Nope @novinosrin - we arrived pretty much at the same time, with similar approaches. Great minds and all...

Re: Date Format troubles

ballardw — Thu, 21 Dec 2017 20:49:25 GMT

And an example of how to do this with a custom INFORMAT, which is think does what you intended. Note that INVALUE is used to read text into a numeric and assign the date literal in the 'ddMONyyyy'd notation.

proc format library=work;
  invalue new_date 
'January 2017'  = '01JAN2017'd
'February 2017' = '01FEB2017'd
'March 2017'    = '01MAR2017'd
'April 2017'    = '01APR2017'd
'May 2017'      = '01MAY2017'd
'June 2017'     = '01JUN2017'd
'July 2017'     = '01JUL2017'd
'August 2017'   = '01AUG2017'd
'September 2017'= '01SEP2017'd
'October 2017'  = '01OCT2017'd
'November 2017' = '01NOV2017'd
'December 2017' = '01DEC2017'd
;
run;


data dummy2;
  infile datalines;
  length Date $14.;
  input Date 1-14;
  sasdate = input(date,new_date.);
  format sasdate mmddyy10.;
  cards;
January 2017
February 2017
March 2017
April 2017
May 2017
June 2017
July 2017
August 2017
September 2017
October 2017
November 2017
December 2017
;;;;
run;

Much lest flexible but the example may be helpful if you get a different set of "months" such that the MONYY. format wouldn't work.

Re: Date Format troubles

Reeza — Thu, 21 Dec 2017 22:15:57 GMT

Building on @ballardw solution, you don't need to create a format manually, it can be dynamically data driven if you use a CNTLIN data set to create the format.

Here's an example of building a custom informat that works with his example:

data date_format;
*set format name and type;
retain fmtname 'monyearc_fmt' type 'I';

*loop through desired years;
do year=2010 to 2020;
    *loop through each month for each year;
    do month=1 to 12;
        * create date variable desired;
        label = mdy(month, 1, year); 
        *Create left hand side of format using formats;
        start = catx(" ", put(label, monname.), put(label, year4.)); 
        *output records to final data set;
        output;
    end;
end;


run;

*create format from dataset above;
proc format cntlin=date_format;
run;

data dummy2;
  infile datalines;
  length Date $14.;
  input Date 1-14;
  sasdate = input(date,monyearc_fmt.);
  format sasdate mmddyy10.;
  cards;
January 2017
February 2017
March 2017
April 2017
May 2017
June 2017
July 2017
August 2017
September 2017
October 2017
November 2017
December 2017
;;;;
run;

You *could* keep it as a character format if you wanted, but then you look some of the flexibility of having a SAS date. It would require changing the TYPE variable as well as the label value to be character instead of a SAS date as presented. The only advantage to this approach would be your pre-existing code would work.

Re: Date Format troubles

Ksharp — Fri, 22 Dec 2017 14:07:16 GMT

If you have sas 9.4, you could try


data dummy2;
  infile datalines;
  length Date $14.;
  input Date 1-14;
  sasdate = input('1 '||date,anydtdte32.);
  format sasdate mmddyy10.;
  cards;
January 2017
February 2017
March 2017
April 2017
May 2017
June 2017
July 2017
August 2017
September 2017
October 2017
November 2017
December 2017
;;;;
run;