Is there a way to get a frequency between 2 variables, where order doesn't matter?

[NR13]

Is there a way to get a frequency between 2 variables, where order doesn't matter?

I have a dataset with 2 ratings. Is there an easy way to get a frequency, that combines when R1/R2 is equivalent to R2/R1? This is a very large dataset -- I'm hoping to avoid typing out "IF R1=1 and R2=2 OR R1=2 and R2=1 THEN ID=1", then doing a proc freq statement.

For example:

 Rating1   Rating2    

    1              2   

    2              1 

    1              3

    1              4

    4              1

would be a count of:

2

1

2

Thanks!

[ballardw]

How many values of Rating1 and Rating2 are there? Are one or more of the values missing for either of those variables? If missing how would the "combination" of the two be treated?

I would be tempted to create a variable combining Rating1 and 2 then custom informat to turn 12 or 21 into a single category. Then I do a count on the formatted value.

[NR13]

it's 0-16 for both ratings - no missing values for either.

[Reeza]

How many observations do you have?

[NR13]

It varies - usually around one thousand

[Reeza · Accepted solution]

That's small....

Data temp;

set have;

first = min(rating1, rating2);

second = max(rating1, rating2);

Run;

proc freq data=temp noprint;

table first*second/out=want;

run;

[ballardw]

And to the OP if you ever need to extend this then the function SMALLEST would come into play.

first      = smallest(1,var1,var2,var3,var4);

second = smallest(2,var1,var2,var3,var4);

third      = smallest(3,var1,var2,var3,var4);

fourth    = smallest(4,var1,var2,var3,var4);

though it would likely  best to declare a couple of arrays for this.

[Astounding]

An easy way:

data want;

set have;

lower_rating = min(Rating1, Rating2);

high_rating = max(Rating1, Rating2);

run;

proc freq data=want;

tables lower_rating * higher_rating / list;

run;

If you have missing values, however, this treats the missing rating as if it were the same as the rating that is not missing.

[NR13]

Thank you all!

[Reeza]

@AstoundingGreat minds think alike 🙂