topic Re: Insert empty rows until certain conditions met in SAS Programming

Insert empty rows until certain conditions met

_MooMoo — Sat, 06 Mar 2021 20:07:03 GMT

Hello SAS communities!

I have following data set.

id	start	end	X
1	1/15/2021	1/21/2021	1/02/2021
2	1/22/2021	1/28/2021	1/09/2021

And I would like to output following data set.

id	start	end	X
1	1/01/2021	1/07/2021	1/02/2021
1	1/08/2021	1/14/2021	1/02/2021
1	1/15/2021	1/21/2021	1/02/2021
2	1/08/2021	1/14/2021	1/09/2021
2	1/15/2021	1/21/2021	1/09/2021
2	1/22/2021	1/28/2021	1/09/2021

The rule is.... if X is not in between start and end, create rows until X is in between start and end. For example, from the above table, rows in bold are added.

Any type of advice is greatly appreciated! Thank you!

Re: Insert empty rows until certain conditions met

Shmuel — Sat, 06 Mar 2021 20:19:33 GMT

You supplied the rule when to add observation. But what are the rules to define START and END for each added line, while X seems to be retained and copied to those added lnes.

Re: Insert empty rows until certain conditions met

Tom — Sat, 06 Mar 2021 20:36:53 GMT

Looks like you want to move the dates by one week at a time. So calculate the number of weeks and run a DO loop.

data have;
  input id (start  end x) (:mmddyy.);
  format start end x yymmdd10.;
cards;
1 1/15/2021 1/21/2021 1/02/2021
2 1/22/2021 1/28/2021 1/09/2021
;

data want ;
  set have ;
  diff = intck('week',start,x);
  do offset=min(0,diff) to max(0,diff) ;
    new_start = intnx('week',start,offset,'s');
    new_end   = intnx('week',end,offset,'s');
    output;
  end;
  format new_start new_end yymmdd10.;
  rename start=original_start end=original_end
         new_start=start new_end=end
  ;
run;

proc print;
run;

              original_     original_
Obs    id         start           end             x    diff    offset         start           end

 1      1    2021-01-15    2021-01-21    2021-01-02     -2       -2      2021-01-01    2021-01-07
 2      1    2021-01-15    2021-01-21    2021-01-02     -2       -1      2021-01-08    2021-01-14
 3      1    2021-01-15    2021-01-21    2021-01-02     -2        0      2021-01-15    2021-01-21
 4      2    2021-01-22    2021-01-28    2021-01-09     -2       -2      2021-01-08    2021-01-14
 5      2    2021-01-22    2021-01-28    2021-01-09     -2       -1      2021-01-15    2021-01-21
 6      2    2021-01-22    2021-01-28    2021-01-09     -2        0      2021-01-22    2021-01-28

Re: Insert empty rows until certain conditions met

_MooMoo — Sat, 06 Mar 2021 21:25:48 GMT

Superb! Thanks.