Add hypen in month year dates (e.g. Jan-20) and another format to J 20

[ywon111]

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 · Accepted solution]

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

---

[ballardw]

---

@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.

[Reeza]

Good to know! I was using SAS On Demand and it didn't work there 😞