Have table:
CompanyID | YEAR | DE | DA | WC | Type | Ratio_Type |
1 | 1996 | 0.448745476 | 0.22336778 | 1.474 | Max. DE | 2 |
1 | 1996 | 0.448745476 | 0.22336778 | 1.474 | Max. DA | 0.5 |
1 | 1996 | 0.448745476 | 0.22336778 | 1.474 | Min. WC | 1.5 |
1 | 1996 | 0.448745476 | 0.22336778 | 1.474 | Max. DE | 2 |
1 | 1996 | 0.448745476 | 0.22336778 | 1.474 | Max. DA | 0.5 |
1 | 1996 | 0.448745476 | 0.22336778 | 1.474 | Min. WC | 1.5 |
1 | 1997 | 0.648033979 | 0.26507138 | 1.474 | Max. DE | 2 |
1 | 1997 | 0.648033979 | 0.26507138 | 1.474 | Max. DA | 0.5 |
1 | 1997 | 0.648033979 | 0.26507138 | 1.474 | Min. WC | 1.5 |
2 | 1997 | 0.648033979 | 0.26507138 | 1.474 | Max. DE | 2 |
2 | 1997 | 0.648033979 | 0.26507138 | 1.474 | Max. DA | 0.5 |
2 | 1997 | 0.648033979 | 0.26507138 | 1.474 | Min. WC | 1.5 |
2 | 1997 | 0.648033979 | 0.26507138 | 1.474 | Max. DE | 2 |
2 | 1997 | 0.648033979 | 0.26507138 | 1.474 | Max. DA | 0.5 |
2 | 1997 | 0.648033979 | 0.26507138 | 1.474 | Min. WC | 1.5 |
2 | 1997 | 0.648033979 | 0.26507138 | 1.474 | Max. DA | 0.6 |
2 | 1997 | 0.648033979 | 0.26507138 | 1.474 | Min. WC | 1.5 |
I would like to match the type column with the previous 3 columns and calculate the difference betwee the actual ratio and the maximum or minimum ratio.
The want table is the following:
CompanyID | YEAR | DE | DA | WC | Type | Ratio_Type | Difference |
1 | 1996 | 0.448745476 | 0.22336778 | 1.474 | Max. DE | 2 | 1.551255 |
1 | 1996 | 0.448745476 | 0.22336778 | 1.474 | Max. DA | 0.5 | 0.276632 |
1 | 1996 | 0.448745476 | 0.22336778 | 1.474 | Min. WC | 1.5 | -0.026 |
1 | 1996 | 0.448745476 | 0.22336778 | 1.474 | Max. DE | 2 | 1.551255 |
1 | 1996 | 0.448745476 | 0.22336778 | 1.474 | Max. DA | 0.5 | 0.276632 |
1 | 1996 | 0.448745476 | 0.22336778 | 1.474 | Min. WC | 1.5 | -0.026 |
1 | 1997 | 0.648033979 | 0.26507138 | 1.474 | Max. DE | 2 | 1.351966 |
1 | 1997 | 0.648033979 | 0.26507138 | 1.474 | Max. DA | 0.5 | 0.234929 |
1 | 1997 | 0.648033979 | 0.26507138 | 1.474 | Min. WC | 1.5 | -0.026 |
2 | 1997 | 0.648033979 | 0.26507138 | 1.474 | Max. DE | 2 | 1.351966 |
2 | 1997 | 0.648033979 | 0.26507138 | 1.474 | Max. DA | 0.5 | 0.234929 |
2 | 1997 | 0.648033979 | 0.26507138 | 1.474 | Min. WC | 1.5 | -0.026 |
2 | 1997 | 0.648033979 | 0.26507138 | 1.474 | Max. DE | 2 | 1.351966 |
2 | 1997 | 0.648033979 | 0.26507138 | 1.474 | Max. DA | 0.5 | 0.234929 |
2 | 1997 | 0.648033979 | 0.26507138 | 1.474 | Min. WC | 1.5 | -0.026 |
2 | 1997 | 0.648033979 | 0.26507138 | 1.474 | Max. DA | 0.6 | 0.334929 |
2 | 1997 | 0.648033979 | 0.26507138 | 1.474 | Min. WC | 1.5 | -0.026 |
Please note that the difference is calculated differently for the Min. and Max. ratios.
Sorry, didn't see the workbook. The following should do what you want:
proc sql;
create table want as
select *,
case
when scan(type,1,' ') eq 'Max.' and scan(type,2,' ') eq 'DE' then ratio_type-DE
when scan(type,1,' ') eq 'Min.' and scan(type,2,' ') eq 'DE' then DE-ratio_type
when scan(type,1,' ') eq 'Max.' and scan(type,2,' ') eq 'DA' then ratio_type-DA
when scan(type,1,' ') eq 'Min.' and scan(type,2,' ') eq 'DA' then DA-ratio_type
when scan(type,1,' ') eq 'Max.' and scan(type,2,' ') eq 'WC' then ratio_type-WC
when scan(type,1,' ') eq 'Min.' and scan(type,2,' ') eq 'WC' then WC-ratio_type
end as difference
from have
;
quit;
HTH,
Art, CEO, AnalystFinder.com
If I can get the SAS SQL code for this I would really appreciate it.
Using your example data, please show how the difference is calculated (for at least one of the min and max examples)
Sorry I attached an Excel file. Difference in cell H2=G2-C2.
Difference= the value from the row of "Ratio_Type" minus the value from the DE column if the value in Type column is equal to "Max. DE".
Sorry, didn't see the workbook. The following should do what you want:
proc sql;
create table want as
select *,
case
when scan(type,1,' ') eq 'Max.' and scan(type,2,' ') eq 'DE' then ratio_type-DE
when scan(type,1,' ') eq 'Min.' and scan(type,2,' ') eq 'DE' then DE-ratio_type
when scan(type,1,' ') eq 'Max.' and scan(type,2,' ') eq 'DA' then ratio_type-DA
when scan(type,1,' ') eq 'Min.' and scan(type,2,' ') eq 'DA' then DA-ratio_type
when scan(type,1,' ') eq 'Max.' and scan(type,2,' ') eq 'WC' then ratio_type-WC
when scan(type,1,' ') eq 'Min.' and scan(type,2,' ') eq 'WC' then WC-ratio_type
end as difference
from have
;
quit;
HTH,
Art, CEO, AnalystFinder.com
Thanks Art:
I simplified the table and consolidated the rows.
I have the following table:
CompanyID | YEAR | DE | DA | WC | Max. DE | Max. DA | Min. WC |
1 | 1996 | 0.448745476 | 0.22336778 | 1.474 | 2 | 1 | |
1 | 1997 | 0.448745476 | 0.22336778 | 1.474 | 1 | ||
2 | 1996 | 0.648033979 | 0.26507138 | 1.474 | 1.5 | 6 | 7 |
2 | 1997 | 0.648033979 | 0.26507138 | 1.474 | 1 |
I want the table:
CompanyID | YEAR | DE | DA | WC | Max. DE | Max. DA | Min. WC | Diff_DE | Diff_DA | Diff_DC |
1 | 1996 | 0.448745476 | 0.22336778 | 1.474 | 2 | 1 | 1.551255 | 0.474 | ||
1 | 1997 | 0.448745476 | 0.22336778 | 1.474 | 1 | 0.551255 | ||||
2 | 1996 | 0.648033979 | 0.26507138 | 1.474 | 1.5 | 6 | 7 | 0.851966 | 5.734929 | -5.526 |
2 | 1997 | 0.648033979 | 0.26507138 | 1.474 | 1 | 0.734929 |
Is this code going to be the same? I also have the attachment.
No, but the following code would:
proc sql;
create table want as
select *,
Max_DE-DE as Diff_DE,
Max_DA-DA as Diff_DA,
WC-Min_WC as Diff_DC
from have
;
quit;
Art, CEO, AnalystFinder.com
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.