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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.