Solved
New Contributor
Posts: 2

# Increment by 1

[ Edited ]

Hi

I am new toSAS. I have the following scenario and would require help to resolve.

Input file:

EMPID EFFDT       EFFSEQ    ACTION
Emp1 2016-01-01     5               H
Emp1 2016-01-01     7               M
Emp1 2016-01-01     8               R
Emp1 2016-02-02     0              12
Emp1 2016-05-04     3              12
Emp1 2016-05-04     4              13

If an Employee has a EFFSEQ starting with 5 in the input file for a particular EFFDT, then his EFFSEQ should increment like 5,6,7...

If an Employee has a EFFSEQ starting other than 5 in the input file for a particular EFFDT, then his EFFSEQ should increment like 0,1,2..

Here in this case, I would expect the below output.

EMPID EFFDT      EFFSEQ ACTION
Emp1 2016-01-01    5            H
Emp1 2016-01-01    6            M
Emp1 2016-01-01    7            R
Emp1 2016-02-02    0           12
Emp1 2016-05-04    0           12
Emp1 2016-05-04    1           13

Accepted Solutions
Solution
‎06-16-2016 05:56 AM
Super User
Posts: 10,280

## Re: Increment by 1

``````data have;
input
empid : \$
effdt : yymmdd10.
effseq
action : \$
;
format effdt yymmddd10.;
cards;
Emp1 2016-01-01 5 H
Emp1 2016-01-01 7 M
Emp1 2016-01-01 8 R
Emp1 2016-02-02 0 12
Emp1 2016-05-04 3 12
Emp1 2016-05-04 4 13
;
run;

proc sort data=have;
by empid effdt effseq;
run;

data want;
format empid effdt effseq action; * for variable order;
set have (rename=(effseq=oldeffseq));
by effdt;
retain effseq;
if first.effdt
then do;
if oldeffseq = 5
then effseq = 5;
else effseq = 0;
end;
else effseq + 1;
drop oldeffseq;
run;``````
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

All Replies
Solution
‎06-16-2016 05:56 AM
Super User
Posts: 10,280

## Re: Increment by 1

``````data have;
input
empid : \$
effdt : yymmdd10.
effseq
action : \$
;
format effdt yymmddd10.;
cards;
Emp1 2016-01-01 5 H
Emp1 2016-01-01 7 M
Emp1 2016-01-01 8 R
Emp1 2016-02-02 0 12
Emp1 2016-05-04 3 12
Emp1 2016-05-04 4 13
;
run;

proc sort data=have;
by empid effdt effseq;
run;

data want;
format empid effdt effseq action; * for variable order;
set have (rename=(effseq=oldeffseq));
by effdt;
retain effseq;
if first.effdt
then do;
if oldeffseq = 5
then effseq = 5;
else effseq = 0;
end;
else effseq + 1;
drop oldeffseq;
run;``````
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Frequent Contributor
Posts: 146

## Re: Increment by 1

Hi Saranya,

Your problem could be solved with a proper sorting and using first. statment.

The first think that you need to do is sort data

``````proc sort data=have;
by EMPID EFFDT EFFSEQ;
run;``````

After that, you data will be sorted. Now you are able to use the statement FIRST. in a data step, that identificate the first element in a group. In that case you want to identify first EFFDT. Depending on if it is the first EFFDT or not, you must operate.

You could do something like

``````data Want;
Set have;
By EMPID EFFDT EFFSEQ;

retain count;

if first.EFFDT and EFFSEQ=5 then EFFSEQ=count;
else if first.EFFDT and EFFSEQ ne 5 then count=0;
else if not first.EFFDT  then count=count+1;
run;``````

This is going to generate the variable that you need.

Super User
Posts: 10,787

## Re: Increment by 1

[ Edited ]
```There are too many scenarios you need to consider about.

```
New Contributor
Posts: 2

## Re: Increment by 1

Hi KurtBremser/Arodriguez/Ksharp,

KurtBremser's solution seemed to work. Anyways i'm checking the other solutions too.

Thanks,
Saranya

🔒 This topic is solved and locked.