Hey guys, I'm struggling with building a counter variable. What I have to do is count the number of years an employee has been in a certain position in a certain department, without counting the employees who were in that position previously. I only need continuous years of employment, if there is a break in employment, then I would only want to count from the most recent hire year. My variables are: year, department, position, emp_id,. After running a proc sql to pull the employees I ran a proc sort and then this data step: data makegroup;
set match;
countvar +1;
by year department position emp_id;
if first.year then countvar=1;
run; This unfortunately starts a counter from 1 all the way to 40k+ .... The next step I have proc sql;
create table want as
select year, department, position, emp_id,
count(countvar) as total_years
from makegroup
group by year, department, position, emp_id
having year = max(year)
order by year, department, position, emp_id
;
quit; Which is great except it gives me the employees that were previously in that position and it doesn't account for breaks in employment. Any help would be much appreciated! Thank you Edit: For example: in this table there is a break between 0708 and 0910, so I want to count 9 years, instead my code counts for 12. year department position emp_id 506 sales assist ls3891g 607 sales assist ls3891g 708 sales assist ls3891g 910 sales assist ls3891g 1011 sales assist ls3891g 1112 sales assist ls3891g 1213 sales assist ls3891g 1314 sales assist ls3891g 1415 sales assist ls3891g 1516 sales assist ls3891g 1617 sales assist ls3891g 1718 sales assist ls3891g
... View more
