DATA Step, Macro, Functions and more

Increment by 1

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

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

 

Thanks in advance.


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

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

View solution in original post


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

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
Frequent Contributor
Posts: 144

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,035

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,

 

Thanks All for your replies Smiley Happy

 

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

 

 

Thanks,
Saranya

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 304 views
  • 0 likes
  • 4 in conversation