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

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;
1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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. 

View solution in original post

6 REPLIES 6
ChrisHemedinger
Community Manager

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;
It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
novinosrin
Tourmaline | Level 20

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,

ChrisHemedinger
Community Manager

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

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
ballardw
Super User

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.

 

Reeza
Super User

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. 

Ksharp
Super User

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;

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
  • 6 replies
  • 2613 views
  • 2 likes
  • 6 in conversation