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
if first.subj then flag=1;
else flag=0;
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;
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.