Counting observation by region

[rx5873]

I want to counting different result by region

Data:

Region    Customer_ID     Method

USA          1                      By Hand

USA          1                      Shipping

USA          1                      By Hand

USA          1                      By Hand

USA          2                      By Hand

USA          2                      Shipping

USA          2                      Express

USA          2                      Express

TW            2                      Express

TW            2                      Express

TW            2                      Express

HK             1                      By Hand

HK             2                      By Hand

HK             3                      By Hand

Result should be like that:

Region  Total_Customer Total_Used_Method

USA         2                       3

TW           1                       1

HK           3                        1

Thanks

[Kurt_Bremser]

proc sql;
create table want as
  select
    region,
    count(distinct customer_id) as total_customer,
    count(distinct method) as total_used_method
  from have
  group by region
;
quit;

[rx5873]

if I don't want to use proc sql, what code I can use.
I need to write it by using if statement

[novinosrin]

HI @rx5873  I would choose Proc SQL over Datastep as it's user friendly and convenient for your question. So if may i suggest you should stick to the solution offered by @Kurt_Bremser .

Here is a solution that abuses HASH for fun but shouldn't be ideally a prod version-

data have;
input Region $    Customer_ID     Method  & $10.;
cards;
USA          1                      By Hand

USA          1                      Shipping

USA          1                      By Hand

USA          1                      By Hand

USA          2                      By Hand

USA          2                      Shipping

USA          2                      Express

USA          2                      Express

TW            2                      Express

TW            2                      Express

TW            2                      Express

HK             1                      By Hand

HK             2                      By Hand

HK             3                      By Hand
;
data want;
 if _n_=1 then do;
  dcl hash h();
  h.definekey('Method');
  h.definedone();
 end;
 do _n_=h.clear() by 0 until(last.region);
  set have;
  by region customer_id notsorted;
  Total_Customer=sum(Total_Customer,first.customer_id);
  if h.check() ne 0 then do;
   total_method=sum(total_method,1);
   h.add();
  end;
 end;
 keep region total_:;
run;

[Reeza]

---

@novinosrin wrote:

HI @rx5873  I would choose Proc SQL over Datastep as it's user friendly and convenient for your question. So if may i suggest you should stick to the solution offered by @Kurt_Bremser .

---

When someone has a specified implementation required it often means it's likely either homework or an interview test.

[novinosrin]

Ah I see @Reeza  Hmm makes sense. That didn't cross my mind. Point taken. Thank you!

[ballardw]

---

@rx5873 wrote:
if I don't want to use proc sql, what code I can use.
I need to write it by using if statement

---

Since you seem to specify a specific method then provide an example of what you think is the method.

"if statement" is pretty generic and might be used in any of a dozen ways and we really don't want to "guess" which is the "right" way that you think you want.

Proc freq would be another way to get counts and could be done with two calls to the proc, and do you need a data set for further manipulation or a report for people to read?

[Kurt_Bremser]

Maxim 14: Use the right tool.

You can squeeze it out of PROC FREQ with ODS and TRANSPOSE:

ods select none;
ods output nlevels=freq;
proc freq data=have nlevels;
by region notsorted;
tables customer_id method;
run;
ods select all;
ods output close;

proc transpose data=freq out=want (drop=_name_ _label_);
by region notsorted;
id tablevar;
var nlevels;
run;

or use two PROC SUMMARYs (one for each variable) and merge the results. But nothing is as straight forward as the SQL, so stay with that.

[Angel_Larrion]

You can do that with a data step:

proc sort data=have nodupkey;
by Region Customer_ID  Method ;
run;

data want;
set have;
by region customer_id method;
if first.region then Total_Customer=0;

if first.customer_id then do;
Total_Used_Method=0;
Total_Customer+1;
end;

Total_Used_Method+1;
if last.region then output;
keep region Total_Customer Total_Used_Method ;
run;

[Ksharp]

data have;
input Region $    Customer_ID     Method  & $10.;
cards;
USA          1                      By Hand
USA          1                      Shipping
USA          1                      By Hand
USA          1                      By Hand
USA          2                      By Hand
USA          2                      Shipping
USA          2                      Express
USA          2                      Express
TW            2                      Express
TW            2                      Express
TW            2                      Express
HK             1                      By Hand
HK             2                      By Hand
HK             3                      By Hand
;
data want;
 if _n_=1 then do;
   if 0 then set have;
   declare hash c();
   c.definekey('Customer_ID');
   c.definedone();
   declare hash m();
   m.definekey('Method');
   m.definedone();
 end;
set have;
by region notsorted;
c.ref();m.ref();
if last.region then do;
  Total_Customer=c.num_items;
  Total_Used_Method=m.num_items;
  c.clear();m.clear();
  output;
end;
drop Customer_ID Method;
run;