BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Gladis6680
Obsidian | Level 7

Hi there,

I had a quick question regarding PROC TABULATE. I have the below table

 

FISCAL_PERIODBEVERAGESSALESCOUPONS
JANMILKSHAKES$300$20
JANCOFFEE$150$30
JANTEA$600$10
JANSODA$550$50
JANSMOOTHIE$300$0
JANMILK$200$50
JANJUICE$0$10
FEBMILKSHAKES$0$0
FEBCOFFEE$30$0
FEBTEA$60$3
FEBSODA$70$0
FEBSMOOTHIE$90$10
FEBMILK$100$30
FEBJUICE$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

 JANJANFEBFEB
BEVERAGESSalesCOUPONSSalesCOUPONS
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

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20

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;

View solution in original post

4 REPLIES 4
Astounding
PROC Star

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.

novinosrin
Tourmaline | Level 20

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;
Gladis6680
Obsidian | Level 7

Thank you so much..that worked

ballardw
Super User

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.

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 1131 views
  • 2 likes
  • 4 in conversation