Regarding your first question: Is there a better way to do this for rows? I have not found a better solution on the web. However, I have used your solution to insert multiple rows between two existing rows (current and previous row). Here is my code: /*
Convert year/week variable to a date (Monday in week)
*/
%macro year_week_to_date(year_week, active_libname, table_input, table_output);
proc sql;
create table &active_libname..&table_output as
select
*,
intnx(
'week.2',
(
case when week(mdy(1, 1, input(substr(&year_week, 1, 4), 4.)), 'v') eq 1
then intnx('week.1', mdy(1, 1, input(substr(&year_week, 1, 4), 4.)), 0)
else
mdy(1, 1, input(substr(&year_week, 1, 4), 4.))
end
),
input(substr(&year_week, 5, 2), 2.)
) as year_week_date
format ddmmyyd10.
from &active_libname..&table_input;
quit;
%mend;
options mprint;
%year_week_to_date(year_week, work, any_table, temp0);
/*
Sort data
*/
proc sort data=work.temp0 out=work.temp1;
by a b c d e year_week_date;
run;
/*
Flag the current row if the difference between the current and previous row (within group) is greater or equal to one week
*/
data work.temp2;
set work.temp1;
by a b c d e year_week_date;
group = 0;
delta = intck('week', lag(year_week_date), year_week_date) - 1;
if lag(a) eq a
and lag(b) eq b
and lag(c) eq c
and lag(d) eq d
and lag(e) eq e then group = 1;
if group eq 1 and delta eq 0 then break = 0;
else if group eq 1 and delta ge 1 then break = 1;
else break = 0;
run;
/*
Sort data
*/
proc sort data=work.temp2 out=work.temp3;
by a b c d e descending year_week_date;
run;
/*
Insert new row(s) and update column values
Insert a maximum of 4 rows (business rule)
*/
data work.temp4;
set work.temp3;
by a b c d e descending year_week_date;
output;
if break eq 1 then
do;
do i = 1 to delta until(i = 4);
* Flag a new row;
break = 2;
year_week_date = intnx('week.2', year_week_date, - 1);
f = 'No delivery';
g = 0;
output;
end;
end;
run;
/*
Convert year_week_date to year/week and replace the old year/week variable
*/
data work.temp5;
set work.temp4;
year_week =
put(
year(year_week_date) - (month(year_week_date) = 1 and week(year_week_date, 'v') in(52, 53)) + (month(year_week_date) = 12 and week(year_week_date, 'v') = 1),
4.
)
||
put(
week(year_week_date, 'v'),
z2.
);
run;
/*
Sort data
*/
proc sort data=work.temp5 out=work.output;
by a b c d e year_week_date;
run; Thank you very much for your help 🙂
... View more
