Proc tabulate, Format and Statistics

[Q1983]

data test;
length Category Month $20. ;
input Category $ Month $ Curr Due_30 Paid;
datalines;
Current Jan19 4 0 0
Paid Jan19 0 0 2
Due_30 Feb19 0 7 0
Due_60 Feb19 0 2 0
;run;

output is

Category	Month	Curr	Due_30	Paid
Current	Jan19	4	0	0
Paid	Jan19	0	0	2
Due_30	Feb19	0	7	0
Due_60	Feb19	0	2	0

proc format;
value $catfmt 1='Current'
2='Due_30'
3='Paid'
;
value $monthfmt 1='Jan19'
2 ='Feb19'
3 ='Mar19'
;
value nu
run;

proc tabulate data=test ;
VAR Curr Due_30 Paid;
CLASS Month / order=internal /*ORDER=UNFORMATTED MISSING;*/;
CLASS Category / order=internal /*ORDER=UNFORMATTED MISSING;*/;
TABLE
/* ROW Statement */
Category *(Curr*f=number8.0 Due_30*f=number8.0 Paid*f=number8.0 )
all = 'Total' *(Curr*f=number8.0 Due_30*f=number8.0 Paid*f=number8.0 ),
/* COLUMN Statement */
Month *( Sum={LABEL=""} )
all = 'Total' *( Sum={LABEL=""} ) ;
;
format Category $catfmt. Month $monthfmt. ;
RUN;

1. Despite using proc format to arrange the months in date order, (ie Jan,Feb) the report uses alpha order so Feb comes first

2. I want to should a %roll change based on each month.  For example in Jan19 there were 4 current accounts.  2 of the accounts paid off so I want to show a 50% change.  Since they are different statistics they show in different columns.  Therfore I can not use the lag function so can I address this in the proc tab

[PaigeMiller]

Character variables will sort in alphabetical order. Feb comes before Jan. Your PROC FORMAT does nothing in this case. Your syntax is backwards.

But rather than get into that, what you really want is to use an INFORMAT when you read the MONTH variable, so that MONTH is a numeric date value, which will sort in proper order. Almost every operation in SAS involving calendar dates (or times) is easier to perform if the value is a numeric SAS date (or datetime, or time) value; and almost every operation on calendar dates is much harder if they are character strings.

input Category $ Month :monyy. Curr Due_30 Paid;

Then you need to format the variable MONTH as MONYY.

format month monyy.;

You will likely need the ORDER=UNFORMATTED option on the PROC TABULATE statement.

[ballardw]

Here's what your data would have to look like for that to work:

data test;
length Category Month $20. ;
input Category $ Month $ Curr Due_30 Paid;
datalines;
1 1 4 0 0
3 1 0 0 2
2 2 0 7 0
Due_60 2 0 2 0
;run;


proc format library=work;
value $catfmt 1='Current'
2='Due_30'
3='Paid'
;
value $monthfmt 1='Jan19'
2 ='Feb19'
3 ='Mar19'
; 

run;

proc tabulate data=test ;
VAR Curr Due_30 Paid;
CLASS Month / order=internal /*ORDER=UNFORMATTED MISSING;*/;
CLASS Category / order=internal /*ORDER=UNFORMATTED MISSING;*/;
TABLE 
/* ROW Statement */
Category *(Curr*f=number8.0 Due_30*f=number8.0 Paid*f=number8.0 )
all = 'Total' *(Curr*f=number8.0 Due_30*f=number8.0 Paid*f=number8.0 ),
/* COLUMN Statement */
Month *( Sum={LABEL=""} ) 
all = 'Total' *( Sum={LABEL=""} ) ;
;
format Category $catfmt. Month $monthfmt. ;
RUN;

You really do want to get into the habit of using actual date values instead of random characters. For one thing the tools to create the SAS date value may well tell you have bad data like 30Feb2020 (my favorite like this was I inherited data with 67Aug1974).

Second is when you have a report working the way you want with one format you can regroup the data just by changing the format. So a date that represents (the first day of) a month, can by changing the format be used to create calendar quarter or annual summaries.

Custom formats can also create groups based on values so you could you group the current year by months, last year by calendar quarter and the previous years by year or combine multiple years. Without having to manipulate the actual values at all.

And if it becomes necessary to manipulate dates there are SAS functions that are much easier than trying to figure the rules to work around leap days or months with different number of days.