topic Re: Rowise ratio of two or more variables with respect to a reference column in New SAS User

Rowise ratio of two or more variables with respect to a reference column

NewUsrStat — Sat, 02 Sep 2023 12:11:18 GMT

Hi guys suppose to have the following table:

_FREQ_	Count_1	Count_2
24213	30240	465
21292	58684	16485
11984	4168	332
24981	130459	33411

Is there a way to divide Count_* column by _FREQ_rowise?

Let say: 30240/2413 then 58684/21292.... for Count_1 and

465/24213 then 16485/21292 .... for Count_2 and so on?

Thank you in advance

Re: Rowise ratio of two or more variables with respect to a reference column

Kurt_Bremser — Sat, 02 Sep 2023 12:57:20 GMT

Just run a DATA step with

ratio1 = count1 / _freq_;
ratio2 = count2 / _freq_;

Re: Rowise ratio of two or more variables with respect to a reference column

NewUsrStat — Sat, 02 Sep 2023 13:03:06 GMT

Thank you for your help! I have 28 columns I would like to avoid write the ratios every time

Re: Rowise ratio of two or more variables with respect to a reference column

ballardw — Sat, 02 Sep 2023 13:05:12 GMT

I believe we have suggested providing data in the form of working data step code so we have actual data to work with and don't have to ask questions about it.

If I understand your question I think this is what you want:

data want;
   set have;
   rate1 = count_1 / _freq_;
   rate2 = count_2 / _freq_;
run;

If you have 'many' count or other numerator variables that you want then ARRAY processing can reduce the total amount of code:

data want;
   set have;
   array c (*) count_1 count_2; /*<= list of the variables you have*/
   array rate(2) ;  /* the number in () should match the number of "count" variables*/
   do i=1 to dim(c);
       rate[i] = c[i] / _freq_;
   end;
   drop i;
run;

Which if you had 20 count variables the only changes needed would be 1) the list (note SAS allows several list forms such as Count_1 - Count_20 if the variables are named such to simplify name lists)

and 2) the number of elements in the Rate array.

The array statement will create N variables named <name>1 to <name>N where N is the number in parentheses by default. Or you can explicitly name your variables with a list such as shown for the C array.

The function DIM is a special function that returns the number of defined elements in an array. So can be used in a loop as shown to get each index number and select the desired element from the array.

Re: Rowise ratio of two or more variables with respect to a reference column

Kurt_Bremser — Sat, 02 Sep 2023 13:12:42 GMT

So this is your real issue.

That's what arrays are for:

data want;
set have;
array count {28} count1-count28;
array ratio {28} ratio1-ratio28;
do i = 1 to 28;
  ratio{i} = count{i} / _freq_;
end;
drop i;
run;

But whenever I see such data structures, I suspect that a long layout would be much easier to work with from the beginning, even the PROC SUMMARY code would be simpler.

Re: Rowise ratio of two or more variables with respect to a reference column

NewUsrStat — Sat, 02 Sep 2023 13:29:41 GMT

Thank you very much!