Sorry, Actually my typing had some errors and the new problem should as below (there is some changes), can you please check it: I have the following dataset with following variables: ID (Character) test (character) date (character) result (numeric) ID test date result 101 run 15JUL1978 1 101 run 15JUL1978 2 101 run 15JUL1978 1 101 run 18JUL1976 1 101 run 18JUL1976 1 101 run 18JUL1976 1 101 case 15OCT2014 5 101 case 15OCT2014 5 101 case 15DEC2017 . 102 rub 28MAR2017 7 102 rub 28MAR2017 6 102 rub 29MAR 8 102 rub APR2017 5 need retian statement used or proc sql to create a new variable "MAXRESULT" to retain the maxmimum value (by ID test date( meaning per time point) from the presvois itration and compare with the present one and keep the maximum one and NEED A RESULT Datset LIKE AS BELOW. Also need to pay attention to the incomplete date and missing result. ID test date result MAXRESULT 101 run 15JUL1978 1 1 101 run 15JUL1978 2 2 101 run 15JUL1978 1 2 101 run 18JUL1976 1 1 101 run 18JUL1976 1 1 101 run 18JUL1976 1 1 101 case 15OCT2014 5 5 101 case 15OCT2014 5 5 101 case 15DEC2017 . . 102 rub 28MAR2017 7 7 102 rub 28MAR2017 6 7 102 rub 29MAR 8 8 102 rub APR2017 5 5 Thank you so much from the bottom of my heart.
data have;
input (ID test date) (:$10.) result;
datalines;
101 run 15JUL1978 1
101 run 15JUL1978 2
101 run 15JUL1978 1
101 run 18JUL1976 1
101 run 18JUL1976 1
101 run 18JUL1976 1
101 case 15OCT2014 5
101 case 15OCT2014 5
101 case 15DEC2017 .
102 rub 28MAR2017 7
102 rub 28MAR2017 6
102 rub 29MAR 8
102 rub APR2017 5
;
data want;
set have;
by id date notsorted;
retain max_result;
if first.date then call missing(max_result);
max_result=ifn(missing(result), ., max(max_result,result));
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.