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 |
@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;
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.
@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.
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" )
@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;
@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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.