BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Saranya
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User
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;

View solution in original post

4 REPLIES 4
Kurt_Bremser
Super User
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
Lapis Lazuli | Level 10

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
Super User
There are too many scenarios you need to consider about.


Saranya
Calcite | Level 5

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

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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