topic Re: Calculation of Ratio matrix in SAS Programming

Calculation of Ratio matrix

jitendrakoli — Wed, 21 Apr 2021 10:34:23 GMT

Dear All,

I want to calculate ratio matrix (column values divide by row values) as showing in following table,

Is it possible, if yes please guide me do calculation using SAS code.

Ratio	R1	R2	R3	R4	R5	R6
T1	T1/R1	T1/R2	T1/R3	T1/R4	T1/R5	T1/R6
T2	T2/R1	T2/R2	T2/R3	T2/R4	T2/R5	T2/R6
T3	T3/R1	T3/R2	T3/R3	T3/R4	T3/R5	T3/R6
T4	T4/R1	T4/R2	T4/R3	T4/R4	T4/R5	T4/R6
T5	T5/R1	T5/R2	T5/R3	T5/R4	T5/R5	T5/R6
T6	T6/R1	T6/R2	T6/R3	T6/R4	T6/R5	T6/R6

Thanks

Re: Calculation of Ratio matrix

PaigeMiller — Wed, 21 Apr 2021 10:51:32 GMT

Do you have access to PROC IML?

Can you show us the original data?

Do you want the result to be a SAS dataset, or a matrix within IML, or a report?

Re: Calculation of Ratio matrix

jitendrakoli — Wed, 21 Apr 2021 11:09:41 GMT

Thanks for prompt reply.

Not having PROC IML.

Please find attached data.

Output not wants in matrix, but it should be in one column as below,

Ratio

T1/R1

T2/R1

T3/R1

T4/R1

T5/R1

T6/R1

T1/R2

T2/R2

T3/R2

T4/R2

T5/R2

T6/R2

T1/R3

T2/R3

T3/R3

T4/R3

T5/R3

T6/R3

T1/R4

T2/R4

T3/R4

T4/R4

T5/R4

T6/R4

T1/R5

T2/R5

T3/R5

T4/R5

T5/R5

T6/R5

T1/R6

T2/R6

T3/R6

T4/R6

T5/R6

T6/R6

Thanks,

Re: Calculation of Ratio matrix

FreelanceReinh — Wed, 21 Apr 2021 11:53:54 GMT

Hello @jitendrakoli,

You can use PROC SQL to compute the ratios:

/* Create test data for demonstration */

data have;
input Product $ Value;
cards;
T1 88.9
T2 102.3
T3 85
T4 110.5
T5 104.5
T6 98.5
R1 87.8
R2 117.3
R3 104.5
R4 112.3
R5 98.5
R6 109.5
;

/* Compute ratios */

proc sql;
create table want as
select divide(t.value,r.value) as Ratio
from have(where=(product=:'T')) t,
     have(where=(product=:'R')) r
order by r.product, t.product;
quit;

Feel free to add another SELECT item that identifies the value in variable Ratio, e.g.

select catx('/',t.product,r.product) as Formula length=7, divide(t.value,r.value) as Ratio

Please note how convenient it is (for volunteers answering your questions) to have input datasets available in the form of a DATA step as shown above. Many people are not willing or allowed to download MS Office files because of security concerns.

Edit: Replaced t.value/r.value by divide(t.value,r.value) for more robustness.

Re: Calculation of Ratio matrix

jitendrakoli — Wed, 21 Apr 2021 12:00:40 GMT

Thank you very.

It working fine