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
... View more