DATA Step, Macro, Functions and more

How to flag OBS based on a condition

Reply
Super Contributor
Posts: 272

How to flag OBS based on a condition

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

 

              

Occasional Contributor
Posts: 9

Re: How to flag OBS based on a condition

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;

 

 

Super Contributor
Posts: 272

Re: How to flag OBS based on a condition

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

Trusted Advisor
Posts: 1,372

Re: How to flag OBS based on a condition

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;

 

 

Super Contributor
Posts: 272

Re: How to flag OBS based on a condition

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.

Occasional Contributor
Posts: 9

Re: How to flag OBS based on a condition

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

Ask a Question
Discussion stats
  • 5 replies
  • 123 views
  • 2 likes
  • 3 in conversation