topic Re: How to flag first occurrence of maximum score? in SAS Programming

How to flag first occurrence of maximum score?

sri1 — Fri, 24 Jul 2020 10:48:55 GMT

Hi all,

I have a data set with following records where I have to flag the first occurrence of maximum score.I have used the following code

proc sort data=ex;

by subj score;

run;

data ex_t;

set ex;

by subj score;

if last.subj then flag=1;

else flag=0;

run;

With the above code , the following records(highlighted) gets flagged

SUBJ	Score	Date
004	1	01FEB2018
004	1	09MAR2018
005	1	13APR17
005	1	14JUN2017
005	2	04APR17
006	1	17 SEP2018
006	3	12OCT2017

But when the score is same for all records for a subject I need to get first occurrence

i.e in the above table , for subj 004 I need to get the first record flagged instead of second record.Is there any way I can do this.

Your replies are highly appreciated.

Thanks

Re: How to flag first occurrence of maximum score?

PaigeMiller — Fri, 24 Jul 2020 10:58:48 GMT

if first.subj then flag=1;
else flag=0;

Re: How to flag first occurrence of maximum score?

sri1 — Fri, 24 Jul 2020 11:00:33 GMT

Then for subjects 005 and 006, will not get the records with maximum score

Re: How to flag first occurrence of maximum score?

Ksharp — Fri, 24 Jul 2020 11:34:38 GMT

proc sort data=ex;
by subj descending score;
run;

data ex_t;
set ex;
by subj ;
if first.subj then flag=1;
else flag=0;
run;

Re: How to flag first occurrence of maximum score?

PGStats — Fri, 24 Jul 2020 15:04:38 GMT

Just add date to your sorting order if what you want is the first date with maximum score:

proc sort data=ex;
by subj descending score date;
run;

data ex_t;
set ex;
by subj ;
flag = first.subj;
run;

Re: How to flag first occurrence of maximum score?

Astounding — Fri, 24 Jul 2020 19:44:50 GMT

Here's a somewhat more complex solution. Why go with complexity? Well, it may be that you don't really want to sort your data in SCORE order. Perhaps you want to keep it in order by SUBJ DATE the whole time:

proc sort data=have;
   by subj date;
run;

data want;
   do until (last.subj);
      set have;
      by subj;
      max_score = max(score, max_score);
   end;
   found_max='N';
   do until (last.subj);
      set have;
      by subj;
      if found_max='N' and score=max_score then do;
         flag=1;
         found_max='Y';
      end;
      else flag=0;
      output;
   end;
   drop max_score found_max;
run;

The top and bottom loops both read all observations for the same SUBJ. The top loop finds the maximum SCORE for that SUBJ, and the bottom loop calculates FLAG and outputs observations.