topic Re: Counting observation by region in SAS Programming

Counting observation by region

rx5873 — Mon, 16 Nov 2020 17:06:27 GMT

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 2 By Hand

USA 2 Shipping

USA 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

Re: Counting observation by region

Kurt_Bremser — Mon, 16 Nov 2020 17:18:41 GMT

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;

Re: Counting observation by region

rx5873 — Mon, 16 Nov 2020 17:22:35 GMT

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

Re: Counting observation by region

Angel_Larrion — Mon, 16 Nov 2020 17:55:25 GMT

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;

Re: Counting observation by region

novinosrin — Mon, 16 Nov 2020 17:56:12 GMT

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;

Re: Counting observation by region

ballardw — Mon, 16 Nov 2020 17:56:41 GMT

@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?

Re: Counting observation by region

Kurt_Bremser — Mon, 16 Nov 2020 18:00:16 GMT

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.

Re: Counting observation by region

Reeza — Mon, 16 Nov 2020 18:52:00 GMT

@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.

Re: Counting observation by region

novinosrin — Mon, 16 Nov 2020 19:04:50 GMT

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

Re: Counting observation by region

Ksharp — Tue, 17 Nov 2020 13:04:50 GMT


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;