Hi, I have a large dataset that feeds into a monthly report. How do I format the dates so it has hyphen between month and year and also, simplify it to only show first letter (e.g. J 20, F 20, M 20, A 20, M 20, etc).
E.g.
data report;
input date date9.;
format date date9.;
datalines;
1JAN2021
2JAN2021
1FEB2021
;
run;
Thanks
January June and July are going to be a pain. I'm assuming you actually want 21 though as your data is 2021?
Where are you planning to use these later? Picture formats are one option, a custom function is another or just a manual calculation in a data step. Each have their limitations though, so understanding how you use it later is important.
EDIT: in theory the picture format should work but doesn't for me for some reason. Maybe someone else can debug the format.
data report;
input date date9.;
format date date9.;
datalines;
1JAN2021
2JAN2021
1FEB2021
;
run;
proc format;
picture M_YY_FMT (default=8)
low-high='%1b-%y' (datatype=date)
;
run;
data want;
set report;
date_custom1 = catx("-", put(date, monname1.), put(date, year2.));
date_custom2 = put(date, m_yy_fmt.);
date2 = date;
format date2 m_yy_fmt.;
run;
@ywon111 wrote:
Hi, I have a large dataset that feeds into a monthly report. How do I format the dates so it has hyphen between month and year and also, simplify it to only show first letter (e.g. J 20, F 20, M 20, A 20, M 20, etc).
E.g.
data report;
input date date9.;
format date date9.;
datalines;
1JAN2021
2JAN2021
1FEB2021
;
run;
Thanks
January June and July are going to be a pain. I'm assuming you actually want 21 though as your data is 2021?
Where are you planning to use these later? Picture formats are one option, a custom function is another or just a manual calculation in a data step. Each have their limitations though, so understanding how you use it later is important.
EDIT: in theory the picture format should work but doesn't for me for some reason. Maybe someone else can debug the format.
data report;
input date date9.;
format date date9.;
datalines;
1JAN2021
2JAN2021
1FEB2021
;
run;
proc format;
picture M_YY_FMT (default=8)
low-high='%1b-%y' (datatype=date)
;
run;
data want;
set report;
date_custom1 = catx("-", put(date, monname1.), put(date, year2.));
date_custom2 = put(date, m_yy_fmt.);
date2 = date;
format date2 m_yy_fmt.;
run;
@ywon111 wrote:
Hi, I have a large dataset that feeds into a monthly report. How do I format the dates so it has hyphen between month and year and also, simplify it to only show first letter (e.g. J 20, F 20, M 20, A 20, M 20, etc).
E.g.
data report;
input date date9.;
format date date9.;
datalines;
1JAN2021
2JAN2021
1FEB2021
;
run;
Thanks
@Reeza wrote:
January June and July are going to be a pain. I'm assuming you actually want 21 though as your data is 2021?
Where are you planning to use these later? Picture formats are one option, a custom function is another or just a manual calculation in a data step. Each have their limitations though, so understanding how you use it later is important.
EDIT: in theory the picture format should work but doesn't for me for some reason. Maybe someone else can debug the format.
data report; input date date9.; format date date9.; datalines; 1JAN2021 2JAN2021 1FEB2021 ; run; proc format; picture M_YY_FMT (default=8) low-high='%1b-%y' (datatype=date) ; run; data want; set report; date_custom1 = catx("-", put(date, monname1.), put(date, year2.)); date_custom2 = put(date, m_yy_fmt.); date2 = date; format date2 m_yy_fmt.; run;
@ywon111 wrote:
Hi, I have a large dataset that feeds into a monthly report. How do I format the dates so it has hyphen between month and year and also, simplify it to only show first letter (e.g. J 20, F 20, M 20, A 20, M 20, etc).
E.g.
data report;
input date date9.;
format date date9.;
datalines;
1JAN2021
2JAN2021
1FEB2021
;
run;
Thanks
The M_YY_FMT format appears to work for me as written. The numeric value will right justify as default so may appear to have leading spaces in a table viewer. Changing the Default length to 4 "removes" the leading spaces for the example values.
And agree that January, June and July as well as, March/ May and April/ August are extremely problematic for use with this display format.
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.