- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
I'm trying to create a table where it's listed by cpt (renamed as drug).; then the total cost and total claim count per cpt or drug and the average per claim. How would I do this? Right now I am getting an error:
This is the claims pull:
proc sql;
create table claims
as select
distinct(memberNo) as memberNo_dist,
planPayer,
rollupeventid,
cpt,
cptdescription,
icd,
icdDescription,
sum(cost) as cost_sum,
eventDate,
providerNPI,
providerName,
providerSpecialty
from table
where providerSpecialty = 'Dermatology'
and eventDate between "2020-12-01" and "2021-12-31"
and claimatRisk=1
and planPayer not in ('C')
and cpt like ('J%')
group by memberNo_dist, eventDate;
quit;
/*create a table to show data by drug or cpt code*/
proc sql;
create table clms_bydrug
as select cptdescription as drug,
cost_sum,
count(rollupeventid) as claimct,
cost_sum/claimct as avgperclm
from claims
order by drug, cost_sum, claimct, avgperclm;
quit;
but I get this error:
23
24 proc sql;
25 create table clms_bydrug
26 as select cptdescription as drug,
27 cost_sum,
28 count(rollupeventid) as claimct,
29 cost_sum/clmct as avgperclm
30 from claims
31 order by drug, cost_sum, claimct, avgperclm;
ERROR: The following columns were not found in the contributing tables: claimct.
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
CALCULATED is used before variables that are calculated in this SQL; you don't use it before the names of variables that are in the input table(s). Also, I don't think you can use CALCULATED in GROUP BY
proc sql;
create table clms_bydrug
as select cptdescription as drug,
cost_sum,
count(rollupeventid) as claimct,
cost_sum/calculated claimct as avgperclm
from claims
group by cptdescription
order by cost_sum, calculated claimct, calculated avgperclm;
Paige Miller
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
order by drug, cost_sum, calculated claimct, avgperclm;
Paige Miller
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
ERROR: The following columns were not found in the contributing tables: claimct.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Show us the ENTIRE log for this PROC SQL
Paige Miller
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
24 proc sql;
25 create table clms_bydrug
26 as select cptdescription as drug,
27 cost_sum,
28 count(rollupeventid) as claimct,
29 cost_sum/claimct as avgperclm
30 from claims
31 order by drug, cost_sum, calculated claimct, avgperclm;
ERROR: The following columns were not found in the contributing tables: claimct.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Do you need a GROUP BY in that last query?
You may need a couple more calculated as well....or just reference by column number which is easier 🙂
proc sql;
create table clms_bydrug
as select cptdescription as drug,
cost_sum,
count(rollupeventid) as claimct,
cost_sum/claimct as avgperclm
from claims
group by calculated drug
order by 1, 2, 3, 4;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
This is what I'm getting now:
23 proc sql;
24 create table clms_bydrug
25 as select cptdescription as drug,
26 cost_sum,
27 count(rollupeventid) as claimct,
28 cost_sum/claimct as avgperclm
29 from claims
30 group by calculated drug
31 order by cost_sum, calculated claimct, avgperclm;
ERROR: The following columns were not found in the contributing tables: claimct.
ERROR: The following columns were not found as CALCULATED references in the immediate query: drug.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
32 quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Try the updated code
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
CALCULATED is used before variables that are calculated in this SQL; you don't use it before the names of variables that are in the input table(s). Also, I don't think you can use CALCULATED in GROUP BY
proc sql;
create table clms_bydrug
as select cptdescription as drug,
cost_sum,
count(rollupeventid) as claimct,
cost_sum/calculated claimct as avgperclm
from claims
group by cptdescription
order by cost_sum, calculated claimct, calculated avgperclm;
Paige Miller
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
This worked! It gave me the summary I needed. Thank you!
23 proc sql;
24 create table clms_bydrug
25 as select distinct(cptdescription) as drug,
26 sum(cost_sum) as total,
27 count(rollupeventid) as claimct,
28 calculated total/calculated claimct as avgperclm
29 from claims
30 group by drug
31 order by calculated total, calculated claimct, calculated avgperclm;