## first record - always needs to start at 1

Solved
Occasional Contributor
Posts: 8

# first record - always needs to start at 1

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

Accepted Solutions
Solution
‎03-10-2017 01:30 PM
Super User
Posts: 6,935

## Re: first record - always needs to start at 1

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.

All Replies
PROC Star
Posts: 311

## Re: first record - always needs to start at 1

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;``````
Super User
Posts: 8,220

## Re: first record - always needs to start at 1

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

Solution
‎03-10-2017 01:30 PM
Super User
Posts: 6,935

## Re: first record - always needs to start at 1

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.

☑ This topic is solved.