Hi @elbarto
Here is a code that follows the specified rules. However, I am a bit concerned about the expected results if we strictly follow those rules:
It seems to be a mistake for the action_amount where location_id = 29189: it differs between the have table and the want table.
In addition, for year=2015, firm_id=28013 and location_id=6085, I don't understand why pre_action and pre_action_amount are not set to 0 as according to your rule 1.1
Hope this help a little.
Best,
/* Retrieve min and max year for each unique firm_id and location_id where operate = 0 */
proc sql;
create table op0_year_bound1 as
select distinct firm_id, location_id, operate,
min(year) as start_year,
max(year) as end_year,
0 as action,
0 as action_amount,
0 as new_entry
from have
where operate = 0
group by firm_id, location_id
order by firm_id, location_id;
quit;
/* Create new rows for each year*/
data op0_year_bound2;
set op0_year_bound1;
do year = start_year to end_year + 1;
output;
end;
drop start_year end_year;
run;
/* Insert these new rows in the input dataset where operate = 0 */
data have2;
merge op0_year_bound2 have(where=(operate=0));
by firm_id location_id year operate;
run;
/* Append this dataset to the input dataset where operate ne 0
and output only one row per unique firm_id / location_id / firm
-> select operate = 1 in case of duplicate record */
data have3;
set have (where=(operate ne 0)) have2;
run;
proc sort data=have3;
by firm_id location_id year operate;
run;
data have4;
set have3;
by firm_id location_id year operate;
if last.year then output;
run;
/* Compute pre_action and pre_action_amount flags */
data have5;
set have4;
pre_action = lag(action);
pre_action_amount = lag(action_amount);
if lag(firm_id) ne firm_id or lag(location_id) ne location_id or lag(operate) ne 0 then do;
pre_action = .;
pre_action_amount = .;
end;
if new_entry=1 and pre_action = . and pre_action_amount = . then do;
pre_action = 0;
pre_action_amount = 0;
end;
run;
/* Sort the data */
proc sort data=have5 out=want;
by firm_id location_id year;
run;
... View more
