Solved
Contributor
Posts: 31

# Complicated if/then logic

Below is sample data I have from a larger dataset with a large number of questions.

Question Option Key Perc

1 A Y .5

1 B N .4

1 C N .05

1 D N .05

2 A N .2

2 B Y .3

2 C N .35

2 D N .15

3 A N .1

3 B Y .35

3 C N .05

3 D N .6

Here is a description of the variables.

Question: This is the Question ID variable

Option: This is the Option ID variable. Within each question is 4 Options (A, B, C, D)

Key: Within each question, one of the options is the key (i.e., the correct answer), denoted by "Y". The incorrect options have "N" as their value in the key variable.

Perc: This is the percentage of examinees who selected each option.

I want to create a new variable called "Flag" that indicates if, within a question, there is an incorrect option (Key = N) where the Perc value is higher than the Perc value for the correct option (Key = Y). In the example above, there would be 2 "flagged" options. Question 2, Option C would be flagged because the perc value for that option is 0.35, which is higher than the perc value for Question 2, Option B (0.3), which is the correct answer for Question 2. Also, Question 3, Option D would be flagged because the perc value for that option is 0.6, which is higher than the perc value for Question 3, Option B (0.35), which is the correct answer for Question 3.

How can I do this? Basically, I need the program to identify which option is the correct option within each question, then determine if the perc values for any of the other options within that question are higher than the perc value for the correct option. I'd like the Flag variable to be equal to "X" for all the flagged options, and blank for the unflagged options.

Accepted Solutions
Solution
‎07-24-2014 01:50 PM
Posts: 1,270

## Re: Complicated if/then logic

proc sql;

create table want(drop=max) as

select *,case when perc>b.max and key='N' then 1 else 0 end as flag from have a inner join (

select question,max(perc) as max from have where key='Y' group by question) b

on a.question=b.question;

quit;

All Replies
Super User
Posts: 13,498

## Re: Complicated if/then logic

Is there a reason why the "percentages" for question 3 excede 100%?0 .1+0.35+0.05+0.6=1.1 which if percentages would be 110%.

Does that affect the rules as stated?

Contributor
Posts: 31

## Re: Complicated if/then logic

Sorry, that was just a typo by me. I was having a difficult time copying and pasting the actual data into the discussion board, so I just typed it by hand. The percentages within a question should always add up to 100%. Please pretend that the percentage for Question 3, Option D was 0.5 instead of 0.6.

Posts: 5,519

## Re: Complicated if/then logic

SQL is your friend for this kind of reporting :

data q;

input Question Option \$ Key \$ Perc;

format perc percentn5.1;

datalines;

1 A Y .5

1 B N .4

1 C N .05

1 D N .05

2 A N .2

2 B Y .3

2 C N .35

2 D N .15

3 A N .1

3 B Y .35

3 C N .05

3 D N .6

;

proc sql;

create table opt as

select q1.Question,

case when q2.Perc > q1.Perc then "X" else "" end as flag,

q1.Perc as CorrectPct format=percentn5.1,

q2.perc as WrongPct format=percentn5.1

from

(select Question, Option, Perc from q where Key="Y") as q1 left join

(select Question, Option, Perc from Q where Key="N" group by Question having Perc=max(Perc)) as q2

on q1.Question=q2.Question

select * from opt;

quit;

PG

PG
Solution
‎07-24-2014 01:50 PM
Posts: 1,270

## Re: Complicated if/then logic

proc sql;

create table want(drop=max) as

select *,case when perc>b.max and key='N' then 1 else 0 end as flag from have a inner join (

select question,max(perc) as max from have where key='Y' group by question) b

on a.question=b.question;

quit;

🔒 This topic is solved and locked.