topic Re: How to flag OBS based on a condition in SAS Programming

How to flag OBS based on a condition

knveraraju91 — Mon, 06 Feb 2017 00:40:00 GMT

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

Re: How to flag OBS based on a condition

YiqunDai — Mon, 06 Feb 2017 05:02:51 GMT

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;

Re: How to flag OBS based on a condition

knveraraju91 — Mon, 06 Feb 2017 06:23:26 GMT

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

Re: How to flag OBS based on a condition

Shmuel — Mon, 06 Feb 2017 09:27:08 GMT

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;

Re: How to flag OBS based on a condition

knveraraju91 — Mon, 06 Feb 2017 15:32:45 GMT

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.

Re: How to flag OBS based on a condition

YiqunDai — Mon, 06 Feb 2017 16:10:02 GMT

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