topic Re: Is there a way to get a frequency between 2 variables, where order doesn't matter? in SAS Programming

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

NR13 — Mon, 21 Nov 2016 18:44:37 GMT

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:

Thanks!

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

ballardw — Mon, 21 Nov 2016 19:09:44 GMT

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.

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

NR13 — Mon, 21 Nov 2016 19:12:23 GMT

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

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

Reeza — Mon, 21 Nov 2016 19:18:18 GMT

How many observations do you have?

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

NR13 — Mon, 21 Nov 2016 19:20:22 GMT

It varies - usually around one thousand

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

Reeza — Mon, 21 Nov 2016 19:26:20 GMT

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;

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

Astounding — Mon, 21 Nov 2016 19:28:03 GMT

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.

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

NR13 — Mon, 21 Nov 2016 19:29:42 GMT

Thank you all!

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

Reeza — Mon, 21 Nov 2016 19:30:05 GMT

@AstoundingGreat minds think alike 🙂

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

ballardw — Mon, 21 Nov 2016 20:30:54 GMT

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.