topic Re: selecting different # observations per strata using dataset in SAS Programming

selecting different # observations per strata using dataset

Jay_210 — Fri, 27 Jan 2023 22:00:59 GMT

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.

Re: selecting different # observations per strata using dataset

Tom — Fri, 27 Jan 2023 22:40:20 GMT

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;

Re: selecting different # observations per strata using dataset

ballardw — Fri, 27 Jan 2023 22:43:09 GMT

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).