Best way to summerize? Tabulate, Print, or Report

mcinvalek — Wed, 27 May 2015 14:33:14 GMT

I have a dataset that I wish to summarize, but I'm not sure how to do it and the best method to use. I create something workable in SQL, but PROC SQL is giving me trouble. My guess is that using Report or Tabulate are my best options. I am using SAS 9.3.

Below is my Data, SQL Query, and SQL Output. After some research, it looks like PROC Report may be my best solution.

something like my attempt below. can anyone set me in the right direction?

Proc report data = temp;

column state _1 _2 _3 _4 _5 _6 _7 _8 _9 _10 _11 _12;

define _1 / computed;

compute _1;

_1 = if STORE = 'WALMART' and STATE ^= 'CT' then _1 = TOTAL;

ELSE _1 = '0';

...

run;

DATA

REGION	STATE	STORE	TOTAL	PAY_METHOD	DATE
NE	CT	WALMART	203	CREDIT	01/31/2014
NE	CT	WALMART	185	CREDIT	02/12/2014
SE	SC	WALMART	453	CASH	07/22/2014
SW	AZ	WALMART	491	CASH	02/03/2014
NE	CT	TARGET	156	CREDIT	11/01/2014
NE	CT	TARGET	248	CREDIT	02/05/2014
SE	SC	TARGET	153	CASH	04/06/2014
SW	AZ	TARGET	443	CASH	02/08/2014
NE	CT	SEARS	30	CREDIT	05/20/2014
NE	CT	SEARS	141	CREDIT	12/13/2014
SE	SC	SEARS	95	CASH	08/11/2014
SW	AZ	SEARS	126	CASH	06/01/2014
NE	CT	BEST BUY	393	CREDIT	02/27/2014
NE	CT	BEST BUY	329	CREDIT	09/13/2014
SE	SC	BEST BUY	234	CASH	03/10/2014
SW	AZ	BEST BUY	409	CASH	11/12/2014
NE	CT	AMAZON	476	CREDIT	12/03/2014
NE	CT	AMAZON	209	CREDIT	05/7/2014
SE	SC	AMAZON	378	CASH	06/13/2014
SW	AZ	AMAZON	188	CASH	04/30/2014

SQL Query:

SELECT

CASE WHEN STATE IS NULL THEN 'TOTAL' ELSE STATE END STATE

,SUM(CASE WHEN STORE = 'WALMART' AND STATE <> 'CT' THEN TOTAL ELSE '0' END) AS [1]
,SUM(CASE WHEN STORE = 'TARGET' AND STATE <> 'CT' THEN TOTAL ELSE '0' END) AS [2]

,SUM(CASE WHEN STORE = 'SEARS' AND STATE <> 'CT' THEN TOTAL ELSE '0' END) AS [3]

,SUM(CASE WHEN STORE = 'BEST BUY' AND STATE <> 'CT' THEN TOTAL ELSE '0' END) AS [4]

,SUM(CASE WHEN STORE = 'AMAZON' AND STATE <> 'CT' THEN TOTAL ELSE '0' END) AS [5]

,SUM(CASE WHEN STORE = 'WALMART' AND STATE = 'CT' AND PAY_METHOD = 'CASH' THEN TOTAL ELSE '0' END) AS [6]

,SUM(CASE WHEN STORE = 'TARGET' AND STATE = 'CT' AND PAY_METHOD = 'CASH' THEN TOTAL ELSE '0' END) AS [7]

,SUM(CASE WHEN STORE = 'SEARS' AND STATE = 'CT' AND PAY_METHOD = 'CASH' THEN TOTAL ELSE '0' END) AS [8]

,SUM(CASE WHEN STORE = 'BEST BUY' AND STATE = 'CT' AND PAY_METHOD = 'CASH' THEN TOTAL ELSE '0' END) AS [9]

,SUM(CASE WHEN STORE = 'AMAZON' AND STATE = 'CT' AND PAY_METHOD = 'CASH' THEN TOTAL ELSE '0' END) AS [10]

,SUM(CASE WHEN DATE BETWEEN '2015-01-01' and '2015-12-31' THEN TOTAL ELSE '0' END) AS [11]

,SUM(CASE WHEN STATE = 'CT' THEN TOTAL ELSE '0' END) AS [12]

FROM TEMP

WHERE STATE IS NOT NULL

GROUP BY STATE WITH ROLLUP

ORDER BY CASE WHEN STATE IS NOT NULL THEN 999 ELSE 0 END

OUTPUT

STATE	1	2	3	4	5	6	7	8	9	10	11	12
TOTAL	944	596	221	643	566	0	0	0	0	0	0	2370
AZ	491	443	126	409	188	0	0	0	0	0	0	0
CT	0	0	0	0	0	0	0	0	0	0	0	2370
SC	453	153	95	234	378	0	0	0	0	0	0	0

Re: Best way to summerize? Tabulate, Print, or Report

Ksharp — Thu, 28 May 2015 14:54:31 GMT

It looks like you are doing some proc transpose thing. Try proc report + across . and If you want solution of proc report , better post it at ODS AND REPORT forum . Cynthia is there .

topic Re: Best way to summerize? Tabulate, Print, or Report in SAS Procedures

Best way to summerize? Tabulate, Print, or Report

Re: Best way to summerize? Tabulate, Print, or Report