If you have an existing dataset with bunch of missing values for Parent_Closure, and you want to resolve all of them in one fell swoop, you can do something like this. It will attempt to update your entire dataset for which Parent_Closure is missing.
OPTIONS sqlundopolicy=optional;
PROC SQL;
UPDATE have AS table1
SET Parent_Closure = (SELECT MAX(Parent_Closure)
FROM have AS table2
WHERE table1.Parent_ID = table2.Parent_ID)
WHERE Parent_Closure IS NULL;
QUIT;
OPTIONS sqlundopolicy=required;
Once you have fixed historical data using the above, and you need to grow your dataset (with new observations), you could handle each new observation with the code below. Alternatively, you could keep re-running the above UPDATE statement to fix newly inserted rows.
/* assume you are working with single observation */
DATA one_row_of_new_data;
Parent_ID = 123;
PR_ID = 153;
Value1 = .;
Value2 = .;
Value3 = 45;
Parent_Closure = .;
RUN;
PROC SQL noprint;
/* place the Parent_Closure value in a macro variable */
SELECT MAX(Parent_Closure)
INTO :lookup_parent_closure
FROM have
WHERE Parent_ID = (SELECT Parent_ID FROM one_row_of_new_data);
/* this assumes you are inserting a single observation too */
INSERT INTO have (Parent_ID, PR_ID, Value1, Value2, Value3, Parent_Closure)
SELECT Parent_ID, PR_ID, Value1, Value2, Value3, &lookup_parent_closure
FROM one_row_of_new_data;
RUN;
... View more
