DATA Step, Macro, Functions and more

Date Format troubles

Reply
Frequent Contributor
Posts: 124

Date Format troubles

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;
Community Manager
Posts: 3,344

Re: Date Format troubles

Posted in reply to JediApprentice

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;
PROC Star
Posts: 1,282

Re: Date Format troubles

[ Edited ]
Posted in reply to JediApprentice

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,

Community Manager
Posts: 3,344

Re: Date Format troubles

Posted in reply to novinosrin

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

Super User
Posts: 12,994

Re: Date Format troubles

Posted in reply to JediApprentice

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.

 

Super User
Posts: 22,818

Re: Date Format troubles

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. 

Super User
Posts: 10,609

Re: Date Format troubles

Posted in reply to JediApprentice

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;
Ask a Question
Discussion stats
  • 6 replies
  • 324 views
  • 2 likes
  • 6 in conversation