Increment by 1

[Saranya]

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

Thanks in advance.

[Kurt_Bremser · Accepted solution]

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;

[arodriguez]

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.

[Ksharp]

There are too many scenarios you need to consider about.

[Saranya]

Hi KurtBremser/Arodriguez/Ksharp,

Thanks All for your replies 🙂

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

Thanks,
Saranya