Help using Base SAS procedures

Two Way Proc Report

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 11
Accepted Solution

Two Way Proc Report

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 

 


Accepted Solutions
Solution
‎08-25-2017 08:57 AM
Super User
Super User
Posts: 9,599

Re: Two Way Proc Report

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;

 

View solution in original post


All Replies
Super User
Super User
Posts: 9,599

Re: Two Way Proc Report

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.

Occasional Contributor
Posts: 11

Re: Two Way Proc Report

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

Solution
‎08-25-2017 08:57 AM
Super User
Super User
Posts: 9,599

Re: Two Way Proc Report

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;

 

Occasional Contributor
Posts: 11

Re: Two Way Proc Report

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

SAS Super FREQ
Posts: 825

Re: Two Way Proc Report

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;
Super User
Posts: 10,787

Re: Two Way Proc Report

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;



☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 420 views
  • 0 likes
  • 4 in conversation