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

Posted in reply to knveraraju91

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,554

Re: How to flag OBS based on a condition

Posted in reply to knveraraju91

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
  • 138 views
  • 2 likes
  • 3 in conversation