topic Re: getting count by group in SAS Programming

getting count by group

sg_kr — Tue, 23 Oct 2018 10:33:22 GMT

Hi ,

i have dataset like below.

agt_no	emp_id
1	342
1	342
1	342
1	342
1	342
1	342
1	342
2	342
2	342
2	342
2	342
2	342
2	342
2	342
1	343
1	343
1	343
1	343
2	343
2	343
2	343

i want to find the count of EMP_Id for each agt_no.

output show below:

agt_no	emp_id	total
1	342	7
1	343	4
2	342	7
2	343	3

i tried to do this by using first.emp_id, but it not working accordingly.

rsubmit;

proc sort data=EB_POL;
by emp_id agt_no;
run;

data EB_pol1;
set EB_pol;
by emp_id agt_no ;
if first.emp_id then n=1;
count+1;
run;

endrsubmit;

please help

Re: getting count by group

Kurt_Bremser — Tue, 23 Oct 2018 10:37:28 GMT

Sort by agt_no and emp_id, and use the same by statement in the data step.

For tested code, provide example data in a data step.

Re: getting count by group

PaigeMiller — Tue, 23 Oct 2018 10:39:10 GMT

proc sort data=have;
    by agt_no emp_id;
run;
proc freq data=have;
    by agt_no emp_id;
    table emp_id;
run;

Re: getting count by group

sg_kr — Tue, 23 Oct 2018 10:50:55 GMT

thanks for your response.

but its not working according, the count should change for each emp_id but its not happening.

Re: getting count by group

sg_kr — Tue, 23 Oct 2018 10:51:58 GMT

thanks for your response.

but i want to store the data in a dataset.

Re: getting count by group

VDD — Tue, 23 Oct 2018 11:05:21 GMT

You only need to do the last.agt_no then output.

rsubmit;

proc sort data=EB_POL;
by emp_id agt_no;
run;

data EB_pol1;
set EB_pol;
by emp_id agt_no ;
if first.emp_id then count=0;
count+1;

if last.emp_id then output;
run;

endrsubmit;

corrected to last.emp_id rather than last.agt_no;

Re: getting count by group

sg_kr — Tue, 23 Oct 2018 11:00:49 GMT

thanks for the qlue.

its not last.agt_no.

its last.emp_id;

thank you so much

Re: getting count by group

VDD — Tue, 23 Oct 2018 11:02:14 GMT

thanks for catching that.

Re: getting count by group

PaigeMiller — Tue, 23 Oct 2018 12:17:30 GMT

@sg_kr wrote:

thanks for your response.

but i want to store the data in a dataset.

proc freq data=have;
    by agt_no emp_id;
    table emp_id/noprint out=want;
run;

Re: getting count by group

sg_kr — Tue, 23 Oct 2018 12:26:24 GMT

even this is wrong...the count is not by the emp_id

Re: getting count by group

PaigeMiller — Tue, 23 Oct 2018 12:30:22 GMT

Recommendation: stop trying to write code to do counting when SAS has done this for you. It's called PROC FREQ. It's documented, debugged, has lots of useful features, and performs properly in a bazillion and 32 different real world applications.

Re: getting count by group

Kurt_Bremser — Tue, 23 Oct 2018 12:31:28 GMT

Just to reperat myself:

For tested code, provide example data in a data step.

Re: getting count by group

Kurt_Bremser — Tue, 23 Oct 2018 12:43:10 GMT

And your original data step code has a significant mistake:

data EB_pol1;
set EB_pol;
by emp_id agt_no ;
if first.emp_id then n=1; /* why do you set N to 1 */
count+1; /* but increment COUNT? */
run;

Re: getting count by group

novinosrin — Tue, 23 Oct 2018 14:27:48 GMT

data have;
input agt_no 	emp_id;
cards;
1	342
1	342
1	342
1	342
1	342
1	342
1	342
2	342
2	342
2	342
2	342
2	342
2	342
2	342
1	343
1	343
1	343
1	343
2	343
2	343
2	343
;

proc sql;
create table want as
select agt_no ,	emp_id, count(emp_id) as total
from have
group by  agt_no ,	emp_id
order by  agt_no ,	emp_id;
quit;

Re: getting count by group

novinosrin — Tue, 23 Oct 2018 14:33:25 GMT

data have;
input agt_no 	emp_id;
cards;
1	342
1	342
1	342
1	342
1	342
1	342
1	342
2	342
2	342
2	342
2	342
2	342
2	342
2	342
1	343
1	343
1	343
1	343
2	343
2	343
2	343
;

/*if sorted by agt_no emp_id*/

proc sort data=have;
by  agt_no 	emp_id;
run;

data want;
if 0 then set have;
do total=1 by 1 until(last.emp_id);
set have;
by  agt_no 	emp_id;
end;
run;

Re: getting count by group

novinosrin — Tue, 23 Oct 2018 14:39:12 GMT

Also , since you are giving us the impression you are using combination of by groups making it a distinct pair, notsorted will yield the same results

So with NOTSORTED Option

data want;
if 0 then set have;
do total=1 by 1 until(last.emp_id);
set have;
by  agt_no 	emp_id notsorted;
end;
run;