SAS Programming

DATA Step, Macro, Functions and more
BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
bhca60
Quartz | Level 8

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.
1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26

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

View solution in original post

9 REPLIES 9
PaigeMiller
Diamond | Level 26
order by drug, cost_sum, calculated claimct, avgperclm;
--
Paige Miller
bhca60
Quartz | Level 8
31 order by drug, cost_sum, calculated claimct, avgperclm;
ERROR: The following columns were not found in the contributing tables: claimct.
PaigeMiller
Diamond | Level 26

Show us the ENTIRE log for this PROC SQL

--
Paige Miller
bhca60
Quartz | Level 8
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
Super User

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
Quartz | Level 8

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
Super User

Try the updated code

PaigeMiller
Diamond | Level 26

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
bhca60
Quartz | Level 8

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;

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 9 replies
  • 1094 views
  • 2 likes
  • 3 in conversation