topic Re: Increment by 1 in SAS Programming

Increment by 1

Saranya — Fri, 10 Jun 2016 11:16:49 GMT

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.

Re: Increment by 1

Kurt_Bremser — Fri, 10 Jun 2016 11:22:30 GMT

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;

Re: Increment by 1

arodriguez — Fri, 10 Jun 2016 11:23:40 GMT

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.

Re: Increment by 1

Ksharp — Mon, 13 Jun 2016 07:13:55 GMT

There are too many scenarios you need to consider about.

Re: Increment by 1

Saranya — Mon, 13 Jun 2016 06:20:27 GMT

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