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
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.