DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

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

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!


Accepted Solutions
Solution
‎11-21-2016 02:29 PM
Super User
Posts: 19,861

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

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;

 

View solution in original post


All Replies
Super User
Posts: 11,343

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

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.

Occasional Contributor
Posts: 6

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

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

Super User
Posts: 19,861

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

How many observations do you have?

Occasional Contributor
Posts: 6

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

It varies - usually around one thousand

Solution
‎11-21-2016 02:29 PM
Super User
Posts: 19,861

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

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;

 

Super User
Posts: 11,343

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

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.

Super User
Posts: 5,516

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

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.

Occasional Contributor
Posts: 6

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

Posted in reply to Astounding

Thank you all!

Super User
Posts: 19,861

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

Posted in reply to Astounding

@AstoundingGreat minds think alike Smiley Happy

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 252 views
  • 3 likes
  • 4 in conversation