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!
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;
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.
it's 0-16 for both ratings - no missing values for either.
How many observations do you have?
It varies - usually around one thousand
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;
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.
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.
Thank you all!
@AstoundingGreat minds think alike 🙂
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.