BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Gangi
Obsidian | Level 7

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 

 

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

6 REPLIES 6
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Obsidian | Level 7

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Obsidian | Level 7

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

BrunoMueller
SAS Super FREQ

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
Super User
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;



sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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