getting count by group

[sg_kr]

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

[Kurt_Bremser]

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.

[sg_kr]

thanks for your response.

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

[PaigeMiller]

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

[sg_kr]

thanks for your response.

but i want to store the data in a dataset.

[PaigeMiller]

---

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

[VDD]

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;

[sg_kr]

thanks for the qlue.

its not last.agt_no.

its last.emp_id;

thank you so much

[VDD]

thanks for catching that.

[sg_kr]

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

[PaigeMiller]

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.

[Kurt_Bremser]

Just to reperat myself:

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

[Kurt_Bremser]

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;

[novinosrin]

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;

[novinosrin]

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;