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

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

Jolly — Tue, 25 Aug 2015 19:46:56 GMT

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.

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

Steelers_In_DC — Tue, 25 Aug 2015 20:19:31 GMT

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;

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

hbi — Mon, 14 Sep 2015 19:21:59 GMT

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;