BookmarkSubscribeRSS Feed
knveraraju91
Barite | Level 11

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

 

              

5 REPLIES 5
YiqunDai
Fluorite | Level 6

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;

 

 

knveraraju91
Barite | Level 11

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

Shmuel
Garnet | Level 18

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;

 

 

knveraraju91
Barite | Level 11

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.

YiqunDai
Fluorite | Level 6

Sorry I was asleep yesterday. Thank you for your revision.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 985 views
  • 2 likes
  • 3 in conversation