topic Re: Fill down multipe rows with N calculated with row value in SAS Programming

Fill down multipe rows with N calculated with row value

TeddoVanMierle — Sun, 07 Nov 2021 20:01:03 GMT

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!!

Re: Fill down multipe rows with N calculated with row value

PaigeMiller — Sun, 07 Nov 2021 20:35:55 GMT

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?

Re: Fill down multipe rows with N calculated with row value

yabwon — Sun, 07 Nov 2021 21:01:25 GMT

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

Re: Fill down multipe rows with N calculated with row value

TeddoVanMierle — Mon, 08 Nov 2021 11:51:24 GMT

Hi Bart,

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