Hi elbarto, Perhaps not the optimal solution, and I am also not able to follow 100% the steps that you want to perform. But would something like the below help you to get the "new" rows that you want to include? If the below helps, then afterwards either with RETAIN statement or LAG function you should be able to derive the new variables that you want as needed. Please check as an example. /*Identify unique firm ID, Location ID and year values*/
proc sort data = have nodupkey out = x (keep = firm_id location_id year);
by firm_id location_id year;
where operate = 0;
run;
/*Per firm/location ID keep only first (start) and last (end) year*/
data x2;
set x;
by firm_id location_id;
if first.location_id or last.location_id;
run;
/*Transpose so that per firm/location ID start and end year are on same row*/
proc transpose data = x2 out = x3 (drop = _name_ rename = (col1 = start col2 = end));
var year;
by firm_id location_id;
run;
/*Create a "skeleton" with all years from start to end per firm/location ID*/
data x4 (drop = start end i);
set x3;
by firm_id location_id;
/*For the case of only single year (i.e. end = start)*/
if end = . then end = start;
do i = start to end by 1;
year = i;
output;
end;
run;
/*Merge "skeleton" to original data by firm/location ID and year*/
data have2;
merge have (in = A) x4 (in = B);
by firm_id location_id year;
run;
... View more
