Two Way Proc Report

[Gangi]

I am new SAS EG and trying to create proc report using charcter Varibles,

Table look like below,

market_Area  Market_Region  Customer_Id

east                    A                            12

east                    A                             13

east                    B                             10

east                     C                            17

 and so on

I need report like,

Area         Count(Customer)

East            4

 A                 2

 B                 1

 C                 1

Like this for all regions,

I am trying 

proc report data=Data_Have;
column Market_Area Market_Region Customer_Id;
define Market_Area/group;

define Market_Region/group;
define Customer_Id/sum "Sum of Distinct" width=8;
run;

But its not working, Kndly Help 

[RW9]

I tend to avoid doing data manipulations in proc report.  Especially when doing more complex ones it is just simpler.  For your problem:

data have;
  input market_area $ market_region $ customer_id;
datalines; 
east A 12
east A 13
east B 10
east C 17
;
run;

proc sql;
  create table WANT as
  select  MARKET_AREA as COL1,
          count(CUSTOMER_ID) as RESULT
  from    HAVE
  group by MARKET_AREA
  union all
  select  MARKET_REGION as COL1,
          count(CUSTOMER_ID) as RESULT
  from    HAVE
  group by MARKET_REGION;
quit;

Note how I put the test data in a datastep, ples use that methd in future to convey structure as well as data.

[Gangi]

Thank you for your suggestion, I tried the solution but now It is coming as all the market_areq together and Regions togethere.

I need Market area Followed by the corresponding regions.

Thank you

[RW9 · Accepted solution]

Yes, then you need to apply sorts to the data:

data have;
 input market_area $ market_region $ customer_id;
datalines; 
east A 12
east A 13
east B 10
east C 17
west A 12
west A 13
;
run;

proc sql;
 create table WANT as
 select COALESCE(MARKET_REGION,MARKET_AREA) as COL1,
        RESULT 
 from   (select MARKET_AREA,
                "" as MARKET_REGION,
                count(CUSTOMER_ID) as RESULT
         from HAVE
         group by MARKET_AREA
         union all
         select MARKET_AREA,
                MARKET_REGION,
                count(CUSTOMER_ID) as RESULT
         from HAVE
         group by MARKET_AREA,
                  MARKET_REGION)
 order by MARKET_AREA,MARKET_REGION;
quit;

[Gangi]

Thank  you very much for the Answer.. It is working as expected.

[BrunoMueller]

Hi

You can use Proc REPORT to get what you want. For this you do need two additional computed columns.

In the compute block for the _DUMMY column, we have the logic to fill the column M_NAME with the appropriate content.

Have a look below. If there is not nesting between market area and market region, you could also use Proc TABULATE.

data have;
  input market_area $ market_region $ customer_id;
datalines;
east A 12
east A 13
east B 10
east C 17
west D 12
west E 15
west F 18
;

proc report data=have;
  column market_area market_region m_name customer_id _dummy;
  define market_area / group noprint;
  define market_region / group noprint;
  define m_name / computed ;
  define customer_id / analysis n;
  define _dummy / computed noprint;

  break before market_area / summarize;

  rbreak after / summarize;
  compute m_name / char length=32;
  endcomp;

  compute _dummy / char length=32;
    _dummy = _break_;
    if lowcase(_break_) = "market_area" then do;
      m_name = market_area;
    end;
    if _break_ = " " then do;
      m_name = market_region;
    end;
    if _break_ = "_RBREAK_" then do;
      m_name = "Total";
    end;
  endcomp;
run;

proc tabulate data=have;
  class market_area market_region;
  table
    market_area="Area"
    market_region=" "
    all
    ,
    n
;
run;

[Ksharp]

How about this one?



data have;
  input market_area $ market_region $ customer_id;
datalines; 
east A 12
east A 13
east B 10
east C 17
;
run;
proc report data=have nowd;
column  market_area  market_region  n;
define market_area/group noprint;
define market_region/group;
define n/'count';
compute before market_area;
 market_region=market_area;
endcomp;
break before market_area/summarize;
run;