Fill down multipe rows with N calculated with row value

[TeddoVanMierle]

Good afternoon, 

The goal is to substract the amount of holiday hours, from the hours_worked. 

When the amount of holidays hours are covering multiple days, the initial thought was using a DO UNTIL loop, IF THEN ELSE or using LAG.

But I only get it working using an extra dummy column per substracted day, which isn't efficient when e.g. someone has 120 hours.

The shown format is the format received from the data source.

What we try to achieve is: 

period     holiday      hours worked (8 per day)

9 feb       32            0

10 feb     24            0

11 feb     16            0

12 feb     8              0 

13 feb     0             8

Somebody any idea? 

Thanks in advance!!

[PaigeMiller]

What is the starting data set? If it is the first three columns of what you are showing, please say so clearly. We can't work from screen captures, so it would help if you could provide your starting data set as SAS data step code (instructions).

Can you explain what you want to happen with Feb 3 to Feb 5?

[yabwon · Accepted solution]

Do you try to get something like that:

data have;
period = '01feb2021'd +_N_; 
day = period;
format period yymmdd10. day downame.;
input holiday;
cards;
0
1
2
8
0
0
0
32
0
0
0
0
0
0
0
0
;
run;
proc print;
run;

data want;
  set have;

  holiday + holiday_tmp;

  hours_worked = 8 - holiday;
  label hours_worked = "hours worked (8 per day)";

  holiday_tmp = 0;
  if hours_worked<0 then
    do;
      hours_worked = 0;
      output;
      holiday_tmp + (holiday - 8);
    end;
  else output;

  drop holiday_tmp;
run;

proc print;
run;

Bart

[TeddoVanMierle]

Hi Bart,

Thank you very much! This is exactly the solution we where looking for.