topic Re: Proc Report - Month number as text to month name in SAS Procedures

Proc Report - Month number as text to month name

Jim_Ogilvie — Wed, 08 Jan 2020 13:36:00 GMT

Hello all,

I have been working with proc report and proc sgplot to send output to ODS Excel.

I am summarising some sales data by year and month to give me a comparison of how monthly sales have changes over time.

To get the graph to present month name instead of number, I have used the following:

axis1 label=none
   value=('JAN' 'FEB' 'MAR' 'APR' 'MAY' 'JUN' 'JUL' 'AUG' 'SEP' 'OCT' 'NOV' 'DEC')
   order=1 to 12 by 1
   offset=(2)
   width=1;

In proc report, I can use the same variable to get a nice summary of month across my table, but I can't seem to find a method to get the same results as the graph...

Here's my proc report code:

proc report data=volume_cummulative(where=(year > "&base_year")) split="*"
   style(header) = [font_weight=bold
                    font_size=8pt
                    color=black
                    bordertopstyle=solid
                    bordertopwidth=0.1pt
                    bordertopcolor=black
                    borderbottomstyle=solid
                    borderbottomwidth=0.1
                    borderbottomcolor=black
                    backgroundcolor=cornflowerblue
                    just=center]
   style(column)=[font_size=8pt];
   
   column year month, (cumm /*revenue Invoice_Price*/);
   
   define year / group "Year" style(header)=[just=left] style(column)=[font_weight=bold];
   define month / group across order=internal style(header)=[just=center] "Month";
   define cumm / analysis sum format=comma12.2 "Volume";
 
run;

Any ideas warmly welcomed.

Thanks

Jim

SAS Analytics Pro (9.4 M6)

Re: Proc Report - Month number as text to month name

PaigeMiller — Wed, 08 Jan 2020 13:44:51 GMT

Assuming month is integers 1 through 12 then you can use the MONNAME format.

define month / group across order=internal style(header)=[just=center]
     "Month" format=monname3.;

Re: Proc Report - Month number as text to month name

Jim_Ogilvie — Wed, 08 Jan 2020 13:49:05 GMT

Thanks Paige,

This results in a single column being displayed titled "Jan".

I think the problem here is that monname. is being applied to the integers 1 through to 12 (01JAN1960 to 12JAN1960).

Thanks

Jim

Re: Proc Report - Month number as text to month name

PaigeMiller — Wed, 08 Jan 2020 13:52:22 GMT

Then you don't have integers 1 through 12 as your months.

What are some of the unformatted values of the variable MONTH?

Re: Proc Report - Month number as text to month name

PaigeMiller — Wed, 08 Jan 2020 13:59:15 GMT

Nevermind, I was mistaken about the MONNAME format.

I think you would need a custom format

proc format;
    value monthf 1='Jan' 2='Feb' ... ;
run;

and then use FORMAT=MONTHF. in your PROC REPORT.

Re: Proc Report - Month number as text to month name

Jim_Ogilvie — Wed, 08 Jan 2020 14:08:10 GMT

Many thanks Paige, that has worked!

I had previously tried that, but must have messed up the syntax, as it didn't work...

Happy days. 🙂