I have a monthly table that repeats the past classification for the YTD total days calculation:
Employee | Date | Schedule | YTD Days |
X | 1-Jan-16 | 3WX1W | 31 |
X | 1-Feb-16 | 3WX1W | 60 |
X | 1-Mar-16 | 3WX1W | 91 |
X | 1-Apr-16 | 3WX1W | 121 |
X | 1-May-16 | 3WX1W | 152 |
X | 1-Jun-16 | 3WX1W | 182 |
X | 1-Jul-16 | 3WX1W | 182 |
X | 1-Jul-16 | 4WX4W | 31 |
X | 1-Aug-16 | 3WX1W | 182 |
X | 1-Aug-16 | 4WX4W | 61 |
X | 1-Sep-16 | 3WX1W | 182 |
X | 1-Sep-16 | 4WX4W | 92 |
X | 1-Oct-16 | 3WX1W | 182 |
X | 1-Oct-16 | 4WX2W | 31 |
X | 1-Oct-16 | 4WX4W | 92 |
X | 1-Nov-16 | 3WX1W | 182 |
X | 1-Nov-16 | 4WX2W | 61 |
X | 1-Nov-16 | 4WX4W | 92 |
X | 1-Dec-16 | 3WX1W | 182 |
X | 1-Dec-16 | 4WX2W | 91 |
X | 1-Dec-16 | 4WX4W | 92 |
So from Jan to Jun the employee had the 3WX1W classification, from Jul to Sep the employee was 4WX4W and after Oct the employee is 4WX2W. I to obtain this table:
Employee | Date | Schedule |
X | 1-Jan-16 | 3WX1W |
X | 1-Feb-16 | 3WX1W |
X | 1-Mar-16 | 3WX1W |
X | 1-Apr-16 | 3WX1W |
X | 1-May-16 | 3WX1W |
X | 1-Jun-16 | 3WX1W |
X | 1-Jul-16 | 4WX4W |
X | 1-Aug-16 | 4WX4W |
X | 1-Sep-16 | 4WX4W |
X | 1-Oct-16 | 4WX2W |
X | 1-Nov-16 | 4WX2W |
X | 1-Dec-16 | 4WX2W |
Sorry for making such a basic question, but i am just starting... Thanks a lot in advance!
You will need to provide more description of your.
You show:
X | 1-Jul-16 | 3WX1W | 182 |
X | 1-Jul-16 | 4WX4W | 31 |
And then say "from Jul to Sep the employee was 4WX4W" BUT that is not clear from the shown data as there are two schedule values for Jul. By any chance are these supposed to be different employee values? If so then your example data needs to show some different values. They need not be actual but could be 1, 2, 3 or A, B, C. Something that would let use know which records are associated with different employees.
And the example desired data should be able to be created from your example input data.
I am not sure that I understand what role the YTD days variable has on the problem either.
If I understand the problem, you could solve it by keeping track of the schedules that have already been used. The following produces what you want from the example data you provided:
data have;
infile cards dlm='09'x;
informat date anydtdte9.;
format date date9.;
input Employee $ Date Schedule $ YTD_Days;
cards;
X 1-Jan-16 3WX1W 31
X 1-Feb-16 3WX1W 60
X 1-Mar-16 3WX1W 91
X 1-Apr-16 3WX1W 121
X 1-May-16 3WX1W 152
X 1-Jun-16 3WX1W 182
X 1-Jul-16 3WX1W 182
X 1-Jul-16 4WX4W 31
X 1-Aug-16 3WX1W 182
X 1-Aug-16 4WX4W 61
X 1-Sep-16 3WX1W 182
X 1-Sep-16 4WX4W 92
X 1-Oct-16 3WX1W 182
X 1-Oct-16 4WX2W 31
X 1-Oct-16 4WX4W 92
X 1-Nov-16 3WX1W 182
X 1-Nov-16 4WX2W 61
X 1-Nov-16 4WX4W 92
X 1-Dec-16 3WX1W 182
X 1-Dec-16 4WX2W 91
X 1-Dec-16 4WX4W 92
;
data want (keep=employee date schedule);
set have;
length used $32567;
retain used;
by employee date;
if first.employee then used=schedule;
else if index(used,strip(schedule)) eq 0 then used=catx(' ',used,schedule);
schedule=scan(used,-1," ");
if last.date then output;
run;
HTH,
Art, CEO, AnalystFinder.com
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.