Calculation of Ratio matrix

[jitendrakoli]

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

[PaigeMiller]

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?

[jitendrakoli]

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,

[FreelanceReinh · Accepted solution]

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.

[jitendrakoli]

Thank you very.

It working fine