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.
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;
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;
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.
There are too many scenarios you need to consider about.
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.