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.
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.