Frequent Learner
Posts: 1

# Based on start month and end month, build a monthly table

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!

Super User
Posts: 13,898

## Re: Based on start month and end month, build a monthly table

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.

Super User
Posts: 8,214

## Re: Based on start month and end month, build a monthly table

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

Discussion stats
• 2 replies
• 136 views
• 0 likes
• 3 in conversation