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
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.