I have nearly cracked it using suggestions from sililar posts but my problem appears subtly different and its not working correctly.
What I am trying to do is copy a value for one customer with different sets where some values are missing.
Please note we do not have a name for customer 333 and my code is bringing down customer name from 222 which is incorrect.
eg: dataset ouput given output required
ID set name ID set name ID set name
111 A Fred 111 A Fred 111 A Fred
111 B Fred 111 B Fred 111 B Fred
111 C 111 C Fred 111 C Fred
222 A Jane 222 A Jane 222 A Jane
222 D 222 D Jane 222 D Jane
333 B 333 B Jane 333 B
333 C 333 C Jane 333 C
444 A Daphne 444 A Daphne 444 A Daphne
-------code--------
data rlt_fill2;
set rlt_fill;
by id;
do n=_n_ until (last.id);
if missing(name) then do;
do until (not missing(CRM));
n=n-1;
set rlt_fill (keep=crm) point=n;
end;
end;
end;
run;
Here is one way. This requires the data set to be sorted by ID. The first. (and a corresponding Last. ) is an automatic variable that indicates if the current record represents the first (or last) record of a BY group. So we can process the first record of an ID differently and not assign the name from the previous ID.
data have; infile datalines truncover; input ID set $ name $; datalines; 111 A Fred 111 B Fred 111 C 222 A Jane 222 D 333 B 333 C 444 A Daphne ; run; data want; set have; by id; length lastname $ 8; /* should match length of NAME variable*/ retain lastname ; if first.id then call missing(lastname); if not missing(name) then lastname=name; if missing(name) then name=lastname; drop lastname; run;
Here is one way. This requires the data set to be sorted by ID. The first. (and a corresponding Last. ) is an automatic variable that indicates if the current record represents the first (or last) record of a BY group. So we can process the first record of an ID differently and not assign the name from the previous ID.
data have; infile datalines truncover; input ID set $ name $; datalines; 111 A Fred 111 B Fred 111 C 222 A Jane 222 D 333 B 333 C 444 A Daphne ; run; data want; set have; by id; length lastname $ 8; /* should match length of NAME variable*/ retain lastname ; if first.id then call missing(lastname); if not missing(name) then lastname=name; if missing(name) then name=lastname; drop lastname; run;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.