BookmarkSubscribeRSS Feed
sg_kr
Obsidian | Level 7

Hi ,

i have dataset like below.

 

agt_no emp_id
1342
1342
1342
1342
1342
1342
1342
2342
2342
2342
2342
2342
2342
2342
1343
1343
1343
1343
2343
2343
2343

 

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

 

output show below:

agt_noemp_idtotal
13427
13434
23427
23433

 

 

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

15 REPLIES 15
sg_kr
Obsidian | Level 7

thanks for your response.

 

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

output.PNG

PaigeMiller
Diamond | Level 26
proc sort data=have;
    by agt_no emp_id;
run;
proc freq data=have;
    by agt_no emp_id;
    table emp_id;
run;
--
Paige Miller
sg_kr
Obsidian | Level 7

thanks for your response.

 

but i want to store the data in a dataset.

PaigeMiller
Diamond | Level 26

@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;
--
Paige Miller
VDD
Ammonite | Level 13 VDD
Ammonite | Level 13

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
Obsidian | Level 7

thanks for the qlue.

 

its not last.agt_no.

 

its last.emp_id;

 

thank you so much

VDD
Ammonite | Level 13 VDD
Ammonite | Level 13

thanks for catching that.

sg_kr
Obsidian | Level 7

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

PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
Kurt_Bremser
Super User

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
Tourmaline | Level 20
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
Tourmaline | Level 20
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;

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 15 replies
  • 1673 views
  • 2 likes
  • 5 in conversation