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