Hi,
Following is the data I have in a table where score1 has missing values and I want to pull value from score2 in score1 where Duration1 is between D1 to D6 where duration2 is D6 and score1 where Duration1 is between D7 to D10 from score 2 where duration2 is D10. Can someone please advise how it can be done. Thanks in advance.
current dataset
Duration1 | Score1 | Duration2 | Score2 |
D1 | |||
D2 | |||
D3 | |||
D6 | 50 | D6 | 50 |
D7 | |||
D8 | |||
D9 | |||
D10 | 65 | D10 | 65 |
required output as following
Duration1 | Score1 | Duration2 | Score2 |
D1 | 50 | ||
D2 | 50 | ||
D3 | 50 | ||
D6 | 50 | D6 | 50 |
D7 | 65 | ||
D8 | 65 | ||
D9 | 65 | ||
D10 | 65 | D10 | 65 |
Resort the data in descending sequence, and then use a data step with retain and conditional assignment.
data want; set have; obs=_N_;
proc sort data=want;
by descending obs;
data want;
RETAIN score_new;
set want;
IF score1 NE . THEN score_new = score1;
ELSE score1 = score_new;
proc sort data=want out=want(drop=obs score_new);
by obs;
run;
Thank you
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.