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.
... View more
