BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jsasusr
Fluorite | Level 6

I am trying to loop through a dataset of employee records.  There are 6 different categories (1-6), but  this data needs to be uploaded in a specific way regardless of the categories.   All have to fall into 10 subsets.  For instance, staff cat2 has the 10 subsets, but staf_cat 3 starts at 4.  Is there a way to have all service start at 1 and end at 10?  The counts are the number of employees.  So Staff_Cat2 has Service 3 with 0 employees, which is perfect, but then Staff_cat 7 starts at 2 and 3, then jumps to 7.  I can't figure out if I'm supposed to do a loop or a retain statement.

 

SERVICE STAFF_CAT COUNT SERVICE2
1 2 3  
2 2 5 2
3 2 0 4
4 2 3 4
5 2 3 5
6 2 0 7
7 2 112 7
8 2 4 8
9 2 9 9
10 2 2 10
4 3 3 4
5 3 1 5
6 3 0 7
7 3 59 7
8 3 3 8
9 3 7 9
10 3 2 10
7 4 1 7
2 7 1 2
3 7 0 7
7 7 1 7
8 7 0 11
7 8 4 7
8 8 0 16
7 9 5 7
8 9 0 10
10 9 1 10
7 10 3 7
8 10 0 13

 



DATA WANT;
SET HAVE;
BY STAFF_CATEGORY SERVICE;
IF FIRST.SERVICE THEN COUNT=0;
COUNT+1;
IF LAST.SERVICE;
RUN;

DATA WANT;
SET WANT END=DONE;
OUTPUT;
IF DONE=0;
NEXTOBS=_n_+1;
count=0;
set WANT (keep=SERVICE rename=(SERVICE=SERVICE2)) point=nextobs;
if SERVICE2 > SERVICE +1 then do SERVICE=SERVICE+1 to SERVICE+1;
output;
drop nextobs;
end;
run;

If I haven't said it already, I appreciate your help.  Also, I am using SAS9

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

As always, prefaced by, "If I understand correctly" ...

 

The simplest way would be to create your own shell of a data set with all the categories:

 

proc sql;

create table categories as select distinct staff_cat from have;

run;

data shell;

retain count 0;

set categories;

do service=1 to 10;

   output;

end;

run;

 

Then you can merge in the employee data:

 

data want;

merge shell have;

by staff_cat;

run;

 

The order of the data sets in the MERGE statement is important, so nonzero values for COUNT replace zero values in the shell.

 

View solution in original post

3 REPLIES 3
collinelliot
Barite | Level 11

I'm confused by your description beyond "Is there a way to have all service start at 1 and end at 10?" If that's what you want, I'd do the following. They will count from 1, but they won't end at 10, though, since there aren't ten observations per service. My apologies if I totally misunderstood what you are trying to do.

 

 

data have;
input SERVICE STAFF_CAT COUNT SERVICE2;
datalines;
1 2 3 .
2 2 5 2
3 2 0 4
4 2 3 4
5 2 3 5
6 2 0 7
7 2 112 7
8 2 4 8
9 2 9 9
10 2 2 10
4 3 3 4
5 3 1 5
6 3 0 7
7 3 59 7
8 3 3 8
9 3 7 9
10 3 2 10
7 4 1 7
2 7 1 2
3 7 0 7
7 7 1 7
8 7 0 11
7 8 4 7
8 8 0 16
7 9 5 7
8 9 0 10
10 9 1 10
7 10 3 7
8 10 0 13
;

data want(drop = service rename=(_service = service));
    retain _service;
    set have;
    by staff_cat service;
    if first.staff_cat then _service = 1;
    else _service = _service + 1;
run;
art297
Opal | Level 21

You didn't provide a dataset showing what you want to achieve but, that said, count would do what you stated you want in your first paragraph if you slightly changed your code: i.e., 

 

DATA WANT;
   SET HAVE;
   BY STAFF_CATEGORY;
   IF FIRST.staff_category THEN COUNT=1;
   else COUNT+1;
RUN;

Art, CEO, AnalystFinder.com

Astounding
PROC Star

As always, prefaced by, "If I understand correctly" ...

 

The simplest way would be to create your own shell of a data set with all the categories:

 

proc sql;

create table categories as select distinct staff_cat from have;

run;

data shell;

retain count 0;

set categories;

do service=1 to 10;

   output;

end;

run;

 

Then you can merge in the employee data:

 

data want;

merge shell have;

by staff_cat;

run;

 

The order of the data sets in the MERGE statement is important, so nonzero values for COUNT replace zero values in the shell.

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 775 views
  • 0 likes
  • 4 in conversation