topic Re: PROC TABULATE in SAS Programming

PROC TABULATE

Gladis6680 — Mon, 27 Jan 2020 20:10:46 GMT

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

Re: PROC TABULATE

Astounding — Mon, 27 Jan 2020 20:42:02 GMT

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.

Re: PROC TABULATE

novinosrin — Mon, 27 Jan 2020 20:45:33 GMT


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;

Re: PROC TABULATE

Gladis6680 — Mon, 27 Jan 2020 20:58:25 GMT

Thank you so much..that worked

Re: PROC TABULATE

ballardw — Mon, 27 Jan 2020 22:09:51 GMT

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.