Help using Base SAS procedures

Best way to summerize? Tabulate, Print, or Report

Reply
Frequent Learner
Posts: 1

Best way to summerize? Tabulate, Print, or Report


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

REGIONSTATESTORETOTALPAY_METHODDATE

NE

CTWALMART203CREDIT01/31/2014
NECTWALMART185CREDIT02/12/2014
SESCWALMART453CASH

07/22/2014

SWAZWALMART491CASH02/03/2014
NECTTARGET156CREDIT11/01/2014
NECTTARGET248CREDIT02/05/2014
SESCTARGET153CASH04/06/2014
SWAZTARGET443CASH02/08/2014
NECTSEARS30CREDIT05/20/2014
NECTSEARS141CREDIT12/13/2014
SESCSEARS95CASH08/11/2014
SWAZSEARS126CASH06/01/2014
NECTBEST BUY393CREDIT02/27/2014
NECTBEST BUY329CREDIT09/13/2014
SESCBEST BUY234CASH03/10/2014
SWAZBEST BUY409CASH11/12/2014
NECTAMAZON476CREDIT12/03/2014
NECTAMAZON209CREDIT05/7/2014
SESCAMAZON378CASH06/13/2014
SWAZAMAZON188CASH04/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

                                                                                                                                 
STATE123456789101112
TOTAL9445962216435660000002370
AZ4914431264091880000000
CT000000000002370
SC453153952343780000000
Super User
Posts: 10,044

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

Posted in reply to mcinvalek

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 .

Ask a Question
Discussion stats
  • 1 reply
  • 186 views
  • 0 likes
  • 2 in conversation