Conditional Calculation (SQL)

[Lily]

How would one go about conditional calculation based on other fields? FOr
example
Table 1: Territory_Group, AOI_High, AOI_Low, Factor_High, Factor_Low
Table 2: Territory_Group, Cov_A
I need a statement to give a data set with the following fields:
Territory_Group, Cov_A,
((AOI_High-Cov_A)*Factor_low+(COv_A-AOI_Low)*Factor_High)/(AOI_High-AOI_Low)
as AOI_Factor
Given that Table1. Territory_Group=Table2. Territory_Group AND
(AOI_Low<COV_A<=AOI_High)

Thanks a million!
Lily

 

[Marshall Barton]

How would one go about conditional calculation based on other fields? FOr
example
Table 1: Territory_Group, AOI_High, AOI_Low, Factor_High, Factor_Low
Table 2: Territory_Group, Cov_A
I need a statement to give a data set with the following fields:
Territory_Group, Cov_A,
((AOI_High-Cov_A)*Factor_low+(COv_A-AOI_Low)*Factor_High)/(AOI_High-AOI_Low)
as AOI_Factor
Given that Table1. Territory_Group=Table2. Territory_Group AND
(AOI_Low<COV_A<=AOI_High)

The calculation of the expression should be pretty much the
way you wrote it. The key is to get all the needed fields
from the re;ated records in both tables by specifying the
query's Join clause. The query's SQL statment wil be
something like:

SELECT Table1.Territory_Group, Cov_A,
((AOI_High - Cov_A) * Factor_low +
(COv_A - AOI_Low) * Factor_High) /
(AOI_High - AOI_Low) As AOI_Factor
FROM Table1 INNER JOIN Table2
ON Table1.Territory_Group=Table2.Territory_Group
AND AOI_Low < COV_A And COV_A <= AOI_High

This kind of ON clause can not be specified in the query
design grid so make all further changes in SQL view.

 

[Lily]

Thanks for your help. It works!
Now my problem becomes:
if Cov_A <750 then .............
if Cov_A > 750 then............
I use a union operator to unite both sql

proc sql;
create table Ten_risks_3 as
Select *,
((AOI_High-Cov_A)*H3_low+(COv_A-AOI_Low)*H3_High)/(AOI_High-AOI_Low) as
H3AOI_Factor
from Ten_risks_2 a inner join IPM_MAN.M_AOI b
on a.TerrGrp=b.TerritoryGrp and AOI_Low<=cov_A<AOI_High
Where COv_A <750000
Union
Select *,6.432+.0086*(Cov_A-750000)/1000 as H3AOI_Factor
from Ten_Risks_2 a inner join IPM_MAN.M_AOI b
on a.TerrGrp=b.TerritoryGrp
where Cov_A>=750000;
Quit;

The problem is that I should have get 200 records w/o union, but only got
100 records after union. What is going on here? How can I do it?

THanks,
lily

 

[John Spencer]

Try using UNION ALL instead of UNION and see if that gives you the desired
results. UNION returns DISTINCT records.