- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Tags:
- proc report
- reporting
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Thank you very much for the Answer.. It is working as expected.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;