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 18JUL1976 1
101 run 17JUL1978 2
101 run 20JUL1976 4
101 case 15OCT2014 5
101 case 17NOV2016 4
101 case 15DEC2017 .
102 rub 28MAR2017 7
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 vaue 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 17JUL1978 2 2
101 run 18JUL1976 1 2
101 run 20JUL1976 4 4
101 case 15OCT2014 5 5
101 case 17NOV2016 4 5
101 case 15DEC2017 . 5
102 rub 28MAR2017 7 7
102 rub 29MAR 8 8 (Incomplete Date)
102 rub APR2017 5 8 (Incomplete Date)
Thank you so much from the bottom of my heart.
How do you want to treat incomplete dates? Delete them? Assign a day (e.g. 1 15 or end of month) if missing day? What about for missing year?
Art, CEO, AnalystFinder.com
Retain statement works for me.
data want;
set have;
retain MAXRESULT;
if _n_ = 1 then MAXRESULT=result;
if result > MAXRESULT then MAXRESULT = result;
run;
data have;
input (ID test date) (:$10.) result;
datalines;
101 run 15JUL1978 1
101 run 18JUL1976 1
101 run 17JUL1978 2
101 run 20JUL1976 4
101 case 15OCT2014 5
101 case 17NOV2016 4
101 case 15DEC2017 .
102 rub 28MAR2017 7
102 rub 29MAR 8
102 rub APR2017 5
;
data want;
set have;
by id;
retain max_result;
if first.id then call missing(max_result);
max_result=max(max_result,result);
run;
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.