Solved
Contributor
Posts: 26

# How to flag a row by comparing different records?

Table 1

NameQuestionScore
TonyA76
TonyB60
KellyA81
KellyB90

Table 2

NameQuestionScoreFlag

A98
TonyA76
TonyB60
KellyA81
KellyB90Yes

I'm trying to flag question B if the person has a higher score on question B than A.

For example, table 1 shows that Ada and Kelly both have higher scores on question B than A, so I want create a table like table 2 with flagging question B where score is higher than question A. Can someone please help me how to code this?

Accepted Solutions
Solution
‎07-23-2015 11:41 AM
Posts: 3,167

## Re: How to flag a row by comparing different records?

In that case, 2XDOW will help,

data want;

do until (last.name);

set have;

by name;

if  question='A' then A_score=score;

if question='B' then B_score=score;

end;

do until (last.name);

set have;

by name;

if if question='B'  and B_score > A_score then flag='Yes'; else flag ='No';

output;

end;

drop A_score B_score;

run;

So by doing this, you will be able to compare any two specific scores. Let me know if I misunderstand your need.

All Replies
Super User
Posts: 6,785

## Re: How to flag a row by comparing different records?

Is the order of the observations reliable?  That would mean you would always have data for both questions for each NAME, and question B always follows immediately after question A.

Under those conditions, you could code:

data want;

set have;

increase = dif(score);

if question='B' and increase > 0 then flag='Yes';

run;

If there might be other variations that occur in the data, it would still be possible.  But it would take a little more programming.

Posts: 3,167

## Re: How to flag a row by comparing different records?

if tie is considered as 'Yes', here is an option:

proc sql;

create table want as

select *, case when question='B' and score=max(score) then 'Yes' else ' ' end as flag

from have

group by name

;

quit;

Contributor
Posts: 26

## Re: How to flag a row by comparing different records?

But what if the dataset has more than two questions to compare. For example, I have question A, B, C, and D, and I want to compare the score of A and C and flag the one with higher score. The part that I'm stuck is I don't know how to choose the two scores from two specific questions. My dataset has more than 100 questions and I need to compare two scores from two specific questions.

Solution
‎07-23-2015 11:41 AM
Posts: 3,167

## Re: How to flag a row by comparing different records?

In that case, 2XDOW will help,

data want;

do until (last.name);

set have;

by name;

if  question='A' then A_score=score;

if question='B' then B_score=score;

end;

do until (last.name);

set have;

by name;

if if question='B'  and B_score > A_score then flag='Yes'; else flag ='No';

output;

end;

drop A_score B_score;

run;

So by doing this, you will be able to compare any two specific scores. Let me know if I misunderstand your need.

Contributor
Posts: 26

## Re: How to flag a row by comparing different records?

It works!!!!!! You are awesome. Thank you!

Contributor
Posts: 26

## Re: How to flag a row by comparing different records?

Seems like this can only compare two scores for one name, which mean I can only compare question A & B, not A & B and C & D for the same person. In other words, if I want to compare question A & B and question C & D for Ada then it won't work.

Posts: 3,167

## Re: How to flag a row by comparing different records?

I got lost here. What is your ultimate purpose? comparing a specific pair of answers or comparing any pair of answers? if the latter, then what is the difference from flagging the maximum score out of all of the answer?

Contributor
Posts: 26

## Re: How to flag a row by comparing different records?

I'm sorry that I confused you.

I have multiple pairs of answers that I need to compare per name, such as A compares to B and flag B(the higher score), and C compares to D and flag D (higher score). Not intended to compare A, B , C, D all together. I hope that I clarified.

Super User
Posts: 10,787

## Re: How to flag a row by comparing different records?

You need to specify how many matched groups are there .

### Code: Program.sas

`data have;infile cards truncover expandtabs;input Name \$ Question \$ Score;cards;Ada A 98Ada B 100Ada C 98Ada D 100Tony A 76Tony B 60Kelly A 81Kelly B 90;run;data temp; set have; if Question in ('A' 'B') then group=1;  else if Question in ('C' 'D') then group=2;run;proc sort data=temp;by Name group;run;data want; merge temp temp(rename=(Score=_Score Question=_Q) where=(_Q in ('A' 'C'))); by Name group; if Score gt _Score then flag='Y'; drop _Score _Q group ;run;`

Xia Keshan

Posts: 3,167

## Re: How to flag a row by comparing different records?

You will have several options.

1. One pair a time. Make a Macro out of the code I posted, use answers as input parameters for the macro.

2. All pairs at one time. Then like @xia keshan mentioned, you need to know the total number of the pairs, then create multiple flag variables and temp score variables  for each pair respectively.

Contributor
Posts: 26

## Re: How to flag a row by comparing different records?

what if there are more than two questions (ex. question A, B, C, D)?  I want to compare question A and question C which has the higher score and flag the row with higher score. It seems like your solution work only if there are two questions. How to select the particular questions to compare the score is my major issue. Thank you in advance.

Occasional Contributor
Posts: 6

## Re: How to flag a row by comparing different records?

This may be "old school," but I would use a PROC TRANSPOSE to transpose the different score rows to columns with a resulting file having one row per individual. Columns for SCOREA, SCOREB, SCOREC, etc. The you can use a MAX function to get the highest of the SCORE* values. Does that make sense?

🔒 This topic is solved and locked.