topic Adding total & sorting in Proc Tabulate in SAS Programming

Adding total & sorting in Proc Tabulate

sasuser_sk — Wed, 25 Nov 2020 19:01:08 GMT

Hello Everyone: I'm using proc tabulate to generate a table given below. Few things I would like to ask help for are:

1. Next to contract type an empty column is coming and I don't know why.

2. How can I sort the Month-Year column alphabetically, and

3. How can I add total after each month-year category. I manually created the TOTAL row to give an example on what I want my output to look like.

Thank you for any help. Here is my proc tabulate code I'm using:

proc tabulate data=ABC order=data;

class Month-Year CONTRACT TYPE ORGANIZATION/ MISSING;

table (Month-Year * CONTRACT TYPE )*N='',

( ORGANIZATION all='Total');

run;

		ORGANIZATION		Total
		TYPE A	TYPE B
Month-Year	Contract Type
Jul-20	YEARLY
	MONTHLY
	WEEKLY
	DAILY
TOTAL
Sep-19	YEARLY
	MONTHLY
	WEEKLY
	DAILY
TOTAL
Mar-20	YEARLY
	MONTHLY
	WEEKLY
	DAILY
TOTAL
May-20	YEARLY
	MONTHLY
	WEEKLY
	DAILY
TOTAL
Apr-20	YEARLY
	MONTHLY
	WEEKLY
	DAILY
TOTAL
Aug-20	YEARLY
	MONTHLY
	WEEKLY
	DAILY
TOTAL
Feb-20	YEARLY
	MONTHLY
	WEEKLY
	DAILY
TOTAL

Re: Adding total & sorting in Proc Tabulate

Reeza — Wed, 25 Nov 2020 19:05:25 GMT

That isn't real code....your variable names are not referenced properly.

You can use ALL to get the total, similar to how you get the overall total. Play around with different positioning to see what you want.

proc tabulate data=ABC order=data;

class Month-Year CONTRACT TYPE ORGANIZATION/ MISSING;

table (Month-Year * CONTRACT TYPE all = "Monthly Total" )*N='',

( ORGANIZATION all='Total');

run;

Just to clarify you want your Month-Year to be sorted alphabetically not in order of months in a calendar? Is your variable a SAS date or a character date? If it's a SAS date converting it to character would let it sort alphabetically but that would be very strange IMO.

Re: Adding total & sorting in Proc Tabulate

sasuser_sk — Wed, 25 Nov 2020 19:17:41 GMT

Thanks for reply Reeza. I renamed variables while asking these questions.

My Month-Year is calculated from a date itself and yes I would like to order months as per calendar (sorry for incorrect working).

I tried adding all="total" at every level of variables but could find a perfect hit to add total after each Month-Year category.

I aslo do not know why I see an extra empty column after Contract Type.

Re: Adding total & sorting in Proc Tabulate

Reeza — Wed, 25 Nov 2020 19:24:33 GMT

@sasuser_sk wrote:

Thanks for reply Reeza. I renamed variables while asking these questions.

My Month-Year is calculated from a date itself and yes I would like to order months as per calendar (sorry for incorrect working).

I tried adding all="total" at every level of variables but could find a perfect hit to add total after each Month-Year category.

Did you try the variant I showed in my response? Or something like ((monthYear*variable) all)

I don't know about the empty column, it may depend on the destination and style you're using, working off demo pseudo code without data doesn't really allow for testing and verification just guesses unless you happen to know the exact answer.

Re: Adding total & sorting in Proc Tabulate

sasuser_sk — Wed, 25 Nov 2020 19:32:11 GMT

Yes I tried a few times (below) but got different order of table each time.

(Month-Year *Contract Type)all)*N='',
(organization all="Total" )

(Month-Year all="Total" *Contract Type all="Total" )*N='',
(organization all="Total" )

((Month-Year *Contract Type) all="Total" )*N='',
(organization all="Total" )

Re: Adding total & sorting in Proc Tabulate

ballardw — Wed, 25 Nov 2020 19:46:05 GMT

@sasuser_sk wrote:

Thanks for reply Reeza. I renamed variables while asking these questions.

My Month-Year is calculated from a date itself and yes I would like to order months as per calendar (sorry for incorrect working).

I tried adding all="total" at every level of variables but could find a perfect hit to add total after each Month-Year category.

I aslo do not know why I see an extra empty column after Contract Type.

If you have an actual date use that. Apply a format to the date variable that has the interval you want. I would suggest MONYY7. The groups created by formats will be honored by Proc Tabulate to group multiple dates to a single month or whatever the format shows. BTW 2 digit years are just asking for problems in many places. The order would typically be the calendar order unless you specify an order option on the CLASS statement that overrides the default.

The All typicall goes with the lower nested variable date*(contracttype All) the All then totals the Contract type statistics.

The empty column you see comes from the N statistic with the label suppressed ( N=' '). You could either move the N into a column position or use the table option / Row=float

Continuing with your sort of pseudocode:

proc tabulate data=ABC order=data;
   class datevar CONTRACT TYPE ORGANIZATION/ MISSING;
   format datevar monyy7.;
   table datevar*( CONTRACT TYPE All='Total' )*N='',  
         ( ORGANIZATION all='Total')
         / row=float
  ;
 run;

Re: Adding total & sorting in Proc Tabulate

sasuser_sk — Wed, 25 Nov 2020 21:10:30 GMT

@ballardw and @Reeza Thank you!!!!!. Your expertise are much appreciated.

Below code sorted month-year, removed the extra column and also added total after each contract type.

My date was time stamped. I created field named calculation=datepart(date) and applied MONYY. format to it which worked out perfectly. Awesome learning today.

proc tabulate data=ABC order=data;

class Calculation CONTRACT TYPE ORGANIZATION / MISSING order=internal;

format Calculation monyy.;

table Calculation *( CONTRACT TYPE all='Total'),

( ORGANIZATION all='Total' );

label Calculation='Month-Year' ;

run;