Dear,'
In my data I need to flag all OBS that are after max value for a set of OBS.
The 4th and 5th OBS are after max value (20) for the sort order of obs.(id,visit). Please help. Thank you
data
id date visit value
1 2014-03-18T21:29 1 0
1 2014-03-18T22:29 1 10
1 2014-03-18T22:49 1 20
1 2014-03-18T23:29 1 9
1 2014-03-18T23:39 1 2
output need
id date visit value flag
1 2014-03-18T21:29 1 0
1 2014-03-18T22:29 1 10
1 2014-03-18T22:49 1 20
1 2014-03-18T23:29 1 9 y
1 2014-03-18T23:39 1 2 y
You can use proc sql to find the maxmum of value, and then use retain statement in data step to label "y" for observations after the maximum.
data knveraraju91; input id 1-2 date E8601DT. visit 23-24 value 25-26; cards; 1 2014-03-18T21:29:00 1 0 1 2014-03-18T22:29:00 1 10 1 2014-03-18T22:49:00 1 20 1 2014-03-18T23:29:00 1 9 1 2014-03-18T23:39:00 1 2 ; run;
* use sql to find the maximum of value, and let it be a macro value; proc sql; select max(value) into :max_value from knveraraju91; quit; data data_flag; set knveraraju91; retain flag null; if value = &max_value then flag = 'y'; else flag = flag; run;
proc print data = data_flag; format date E8601DT.; run;
Thank you very much for the support.
Your code also flagging the OBS with maximum value, which is 3rd OBS in my data. I need to flag the OBS after maximum value(4th and 5th in my data). Thank you.
Please help
A small change to @YiqunDai code will do it:
data data_flag;
set knveraraju91;
retain flag null;
output; /* line added */
if value = &max_value then flag = 'y';
/* else flag = flag; <<< this line not needed */
run;
Do you have more than one ID ? If yes then you need addapt it to work per ID:
data data1;
input id 1-2 date E8601DT. visit 23-24 value 25-26;
cards;
1 2014-03-18T21:29:00 1 0
1 2014-03-18T22:29:00 1 10
1 2014-03-18T22:49:00 1 20
1 2014-03-18T23:29:00 1 9
1 2014-03-18T23:39:00 1 2
;
run;
proc sql;
create table data_max as
select id, max(value) as max_value
from data1
group by id;
quit;
data data_flag;
merge data1 data_max;
by id;
retain flag;
if first.id then flag=' ';
output;
if value = max_value then flag = 'y';
run;
Thank you very much. It worked. But I am getting this message "variable null uninitialized". So I removed the null and it works fine. Thank you.
Sorry I was asleep yesterday. Thank you for your revision.
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.