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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.