BookmarkSubscribeRSS Feed
TatianaSilva
Calcite | Level 5

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!

2 REPLIES 2
ballardw
Super User

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.

art297
Opal | Level 21

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

 

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
  • 2 replies
  • 616 views
  • 0 likes
  • 3 in conversation