Date:         Mon, 5 Dec 2005 11:56:41 -0600
Reply-To:     Jiann-Shiun Huang <Jiann-Shiun.Huang@AMERUS.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Jiann-Shiun Huang <Jiann-Shiun.Huang@AMERUS.COM>
Subject:      Re: Help Needed on SQL Code
Comments: To: Michael.F.Murphy@KP.ORG
Content-Type: text/plain; charset=US-ASCII

Michael:

  See if the following works.

data GpCost;
  input MedGp$ cost;
  cards;
1 312800
2 1352490
3 368880
4 88320
5 802650
6 652710
7 1736730
8 906980
9 1051880
10 1034350
11 628720
12 361830
13 1447380
14 132530
15 40430
16 1218940
17 1406440
18 2101310
19 2075450
;
data enroll;
  input Count;
  cards;
456
;
proc sql;
select MedGp, Cost/Count as Average
from GpCost cross join enroll
;
run;


J S Huang

>>> "Michael F. Murphy" <Michael.F.Murphy@KP.ORG> 12/5/2005 11:36:12 AM
>>>
In my example, there are two data sets, one showing cost by MedGp and
the
other showing the number of enrollees. The task is to compute average
costs
(AvgCost) based upon the number of enrollees, as shown in the second
table.

The code MUST be in sql.

 I assume that this is possible using a subquery, but I have not found
a
way to do this. Although the solution is easy using standard SAS code,
I
have not found a way to do this using PROC SQL.

Any suggestions?

MedGp costs
1 312800
2 1352490
3 368880
4 88320
5 802650
6 652710
7 1736730
8 906980
9 1051880
10 1034350
11 628720
12 361830
13 1447380
14 132530
15 40430
16 1218940
17 1406440
18 2101310
19 2075450

Enrollees = 456

MedGp AvgCost
1 685.96
2 2965.99
3 808.95
4 193.68
5 1760.20
6 1431.38
7 3808.62
8 1988.99
9 2306.75
10 2268.31
11 1378.77
12 793.49
13 3174.08
14 290.64
15 88.66
16 2673.11
17 3084.30
18 4608.14
19 4551.43