Hi there,
I had a quick question regarding PROC TABULATE. I have the below table
FISCAL_PERIOD | BEVERAGES | SALES | COUPONS |
JAN | MILKSHAKES | $300 | $20 |
JAN | COFFEE | $150 | $30 |
JAN | TEA | $600 | $10 |
JAN | SODA | $550 | $50 |
JAN | SMOOTHIE | $300 | $0 |
JAN | MILK | $200 | $50 |
JAN | JUICE | $0 | $10 |
FEB | MILKSHAKES | $0 | $0 |
FEB | COFFEE | $30 | $0 |
FEB | TEA | $60 | $3 |
FEB | SODA | $70 | $0 |
FEB | SMOOTHIE | $90 | $10 |
FEB | MILK | $100 | $30 |
FEB | JUICE | $150 | $10 |
I have written the below code
proc tabulate data=HAVE |
class Fiscal_Period BEVERAGES; |
var SALES ; |
table BEVERAGES,FISCAL_PERIOD*SALES*(SUM) |
run; |
and I am able to get the SALES numbers, I would also like to get the COUPONS numbers in there.
so something like this
JAN | JAN | FEB | FEB | |
BEVERAGES | Sales | COUPONS | Sales | COUPONS |
MILKSHAKES | $300 | $20 | $0 | $0 |
COFFEE | $150 | $30 | $30 | $0 |
TEA | $600 | $10 | $60 | $3 |
SODA | $550 | $50 | $70 | $0 |
SMOOTHIE | $300 | $0 | $90 | $10 |
MILK | $200 | $50 | $100 | $30 |
JUICE | $0 | $10 | $150 | $10 |
Is this possible.
Thank you for your help
data have;
infile cards expandtabs truncover;
input (FISCAL_PERIOD BEVERAGES) (:$10.) SALES :dollar10. COUPONS :dollar10. ;
format sales dollar10. COUPONS dollar10. ;
cards;
JAN MILKSHAKES $300 $20
JAN COFFEE $150 $30
JAN TEA $600 $10
JAN SODA $550 $50
JAN SMOOTHIE $300 $0
JAN MILK $200 $50
JAN JUICE $0 $10
FEB MILKSHAKES $0 $0
FEB COFFEE $30 $0
FEB TEA $60 $3
FEB SODA $70 $0
FEB SMOOTHIE $90 $10
FEB MILK $100 $30
FEB JUICE $150 $10
;
proc tabulate data=HAVE;
class Fiscal_Period BEVERAGES;
var SALES coupons ;
table BEVERAGES,FISCAL_PERIOD*SALES*(SUM) FISCAL_PERIOD*COUPONS*(SUM);
run;
A small change should get you there. Here is what you have now:
var SALES ;
table BEVERAGES,FISCAL_PERIOD*SALES*(SUM)
Add COUPONS to the report:
var SALES COUPONS ;
table BEVERAGES,FISCAL_PERIOD*(SALES COUPONS)*(SUM)
As usual, PROC TABULATE gives you lots of tools to format the report. But first see if this gives you the right structure to the report.
data have;
infile cards expandtabs truncover;
input (FISCAL_PERIOD BEVERAGES) (:$10.) SALES :dollar10. COUPONS :dollar10. ;
format sales dollar10. COUPONS dollar10. ;
cards;
JAN MILKSHAKES $300 $20
JAN COFFEE $150 $30
JAN TEA $600 $10
JAN SODA $550 $50
JAN SMOOTHIE $300 $0
JAN MILK $200 $50
JAN JUICE $0 $10
FEB MILKSHAKES $0 $0
FEB COFFEE $30 $0
FEB TEA $60 $3
FEB SODA $70 $0
FEB SMOOTHIE $90 $10
FEB MILK $100 $30
FEB JUICE $150 $10
;
proc tabulate data=HAVE;
class Fiscal_Period BEVERAGES;
var SALES coupons ;
table BEVERAGES,FISCAL_PERIOD*SALES*(SUM) FISCAL_PERIOD*COUPONS*(SUM);
run;
Thank you so much..that worked
This is one of the cases where in the long run you might consider a modified data structure.
Instead of
FISCAL_PERIOD BEVERAGES SALES COUPONS
Something like
FISCAL_PERIOD BEVERAGES Type Amount
Where Type might take on values like
Sales Coupons Promos AgeDiscount Employee
with tabulate code similar to
proc tabulate data=HAVE ; class Fiscal_Period BEVERAGES type; var amount ; table BEVERAGES, FISCAL_PERIOD*type*amount*(SUM) ; run;
I would hope that your Fiscal_period variable is actually a date variable with appropriate format.
That makes it easy to change between annual, quarterly, monthly, week of year, day of week and such summaries: change the format to change the time period.
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.