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
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.