DATA Step, Macro, Functions and more

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

Reply
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:

EmployeeDateScheduleYTD Days
X1-Jan-163WX1W31
X1-Feb-163WX1W60
X1-Mar-163WX1W91
X1-Apr-163WX1W121
X1-May-163WX1W152
X1-Jun-163WX1W182
X1-Jul-163WX1W182
X1-Jul-164WX4W31
X1-Aug-163WX1W182
X1-Aug-164WX4W61
X1-Sep-163WX1W182
X1-Sep-164WX4W92
X1-Oct-163WX1W182
X1-Oct-164WX2W31
X1-Oct-164WX4W92
X1-Nov-163WX1W182
X1-Nov-164WX2W61
X1-Nov-164WX4W92
X1-Dec-163WX1W182
X1-Dec-164WX2W91
X1-Dec-164WX4W92

 

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:

 

EmployeeDateSchedule
X1-Jan-163WX1W
X1-Feb-163WX1W
X1-Mar-163WX1W
X1-Apr-163WX1W
X1-May-163WX1W
X1-Jun-163WX1W
X1-Jul-164WX4W
X1-Aug-164WX4W
X1-Sep-164WX4W
X1-Oct-164WX2W
X1-Nov-164WX2W
X1-Dec-164WX2W

 

Sorry for making such a basic question, but i am just starting... Thanks a lot in advance!

Super User
Posts: 10,500

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.

PROC Star
Posts: 7,363

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

 

Ask a Question
Discussion stats
  • 2 replies
  • 101 views
  • 0 likes
  • 3 in conversation