SAS 9.2 SQL: How to copy values from one row to another by matching IDs

[Jolly]

Hello,

I am wanting to do a step in my program where it will fill in data from one row into another.  I can better explain this by showing what I have and what I would like to get to.

Have:

Parent_ID     PR_ID     Value1     Value2     Value3     Parent_Closure

123                 123           12           29                        01JAN2015

123                 153                                      45

Want:

Parent_ID     PR_ID     Value1     Value2     Value3     Parent_Closure

123                 123           12           29                        01JAN2015

123                 153                                      45           01JAN2015

Thank you,

Jeff S. O.

[Steelers_In_DC]

Here's a solution for your example.  I would test this with your real data to make sure it gets the desired output.

data have;

infile cards dsd;

informat parent_closure date9.;

format parent_closure date9.;

input Parent_ID     PR_ID     Value1     Value2     Value3     Parent_Closure;

cards;

123,123,12,29,,01JAN2015

123,153,,,45,

;

run;

data want;

set have;

by parent_id;

retain date;

if first.parent_id then date = parent_closure;

if missing(parent_closure) then parent_closure = date;

drop date;

run;

[hbi]

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;