I have a data set that has a list of departments and an employee (approximately 100 departments, and 80-160 employees in each department). I want to easily select the first X amount of employees in each department, but X is specific to the department. For instance, Department 1 I may want 17, Dept 2 19, Dept 3 24, and so on. I have a second dataset that has a list of each department and the number of observations I want to select from that department. I do not want them picked randomly, they need to be the first X listed in that department.
Department | Employee |
dept_1 | jim |
dept_1 | jon |
dept_1 | nancy |
dept_1 | eric |
dept_1 | tom |
dept_2 | debbie |
dept_2 | susan |
dept_2 | karen |
dept_2 | don |
dept_2 | nate |
dept_2 | chris |
dept_2 | brian |
dept_3 | valerie |
dept_3 | bob |
dept_3 | mike |
dept_3 | tim |
dept_3 | ryan |
dept_3 | stephanie |
dept_3 | tyler |
Department | Obs_need |
dept_1 | 2 |
dept_2 | 4 |
dept_3 | 5 |
Thanks.
Just merge the two datasets. Keep track of how many are in each department.
data want;
merge big little;
by department;
if first.department then count=0;
count+1;
if count <= obs_need;
run;
Just merge the two datasets. Keep track of how many are in each department.
data want;
merge big little;
by department;
if first.department then count=0;
count+1;
if count <= obs_need;
run;
Maybe:
data want; merge set1 set2; by department; retain depcounter; if first.department then depcounter=1; else depcounter+1; if obs_need le depcounter then output; end;
Set1 is your long set, set2 is the one with obs needed.
This does require both sets to be sorted by the department variable AND that department only occur one time in Set2 (or whatever your name is).
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.