topic Based on start month and end month, build a monthly table in SAS Programming

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

TatianaSilva — Fri, 13 Jan 2017 22:16:37 GMT

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!

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

ballardw — Fri, 13 Jan 2017 22:48:49 GMT

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.

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

art297 — Fri, 13 Jan 2017 23:00:02 GMT

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