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 |
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 .
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 the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.