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;
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.
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;
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,
Nope @novinosrin - we arrived pretty much at the same time, with similar approaches. Great minds and all...
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.
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.
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;
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.
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.