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
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.
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?
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,
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.
Thank you very.
It working fine
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.