Trying to create a table to show a summary

[bhca60]

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.

[PaigeMiller]

order by drug, cost_sum, calculated claimct, avgperclm;

[bhca60]

31 order by drug, cost_sum, calculated claimct, avgperclm;
ERROR: The following columns were not found in the contributing tables: claimct.

[PaigeMiller]

Show us the ENTIRE log for this PROC SQL

[bhca60]

its the same. the only thing I changed was adding the calculated.


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.

[Reeza]

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;

[bhca60]

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

[Reeza]

Try the updated code

[PaigeMiller · Accepted solution]

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;

[bhca60]

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;