Trying to create a table that shows totals for each row

[#1]

I need the table to look like this:

plan category   memberct    total cost  

proc sql;
create table claims
as select
memberNo,
planPayer,
claimHeaderId,
cpt,
cptdescription,
cost,
eventDate,
trim(memberNo)||trim(eventDate) as visitct,
providerNPI,
providerName,
providerSpecialty
from x.eventLineDetails
where providerSpecialty In ('Cardiology', "Cardiovascular Disease")
and eventDate between "2021-01-01" and "2021-12-31"
and claimatRisk=1
and cpt in &EMCODES;
quit;

/*put into 3 buckets*/
data claims2;
set claims;
if planPayer="ddd" then planCategory="ddd2";
else if planPayer="ggg" then planCategory="ggg2";
else planCategory="ooo";
run;

[PaigeMiller]

---

@anonymous_user wrote:

I need the table to look like this:

plan category   memberct    total cost 

---

Show us more of this table, with fake numbers if necessary, so we can grasp what you want to do. Also you want total cost in this table, where does that come from. I think we need a lot more detail and clarity in your explanation.

[#3]

plancategory memberCt totalcost

ddd2 20,0000 1,000,000
ggg2 1,500 500,000
ooo 11,200 250,000

[PaigeMiller]

Ok, if I am understanding everything properly

proc summary data=have nway;
    class plancategory;
    var memberct cost;
    output out=want sum=memberct totalcost;
run;

[#5]

thank you but when i do this it brings back the correct counts under the freq column instead of the memberct column. Should I count the memberNo in the first proc sql step or in the other data step where I define the buckets?

[Kurt_Bremser · Accepted solution]

proc summary data=claims2 nway;
class plancategory;
var cost;
output
  out=want (drop=_type_ rename=(_freq_=membercnt))
  sum(cost)=totalcost
;
run;

[dexcort2020]

data summary data=have nway;
    output plancategory;
    memory memberct cost;
    mem=want sum=memberct totalcost;
run;

[Kurt_Bremser]

---

@dexcort2020 wrote:

data summary data=have nway;
    output plancategory;
    memory memberct cost;
    mem=want sum=memberct totalcost;
run;

---

 69         data summary data=have nway;
                             _
                             22
                             200
 70             output plancategory;
                       ____________
                       455
 71             memory memberct cost;
                ______
                180
 ERROR 22-322: Syntaxfehler, erwartet wird eines der folgenden: ein Name, eine Zeichenkette in Hochkommata, (, /, ;, _DATA_, _LAST_, 
               _NULL_.  
 
 ERROR 200-322: The symbol is not recognized and will be ignored.
 
 ERROR 455-185: Data set was not specified on the DATA statement.
 
 ERROR 180-322: Statement is not valid or it is used out of proper order.
 
 72             mem=want sum=memberct totalcost;
                         ___
                         22
 ERROR 22-322: Syntaxfehler, erwartet wird eines der folgenden: !, !!, &, (, *, **, +, -, /, ;, <, <=, <>, =, >, ><, >=, AND, EQ, 
               GE, GT, IN, LE, LT, MAX, MIN, NE, NG, NL, NOTIN, OR, [, ^=, {, |, ||, ~=.  
 
 72       !     mem=want sum=memberct totalcost;
                                      _________
                                      22
 ERROR 22-322: Syntaxfehler, erwartet wird eines der folgenden: !, !!, &, *, **, +, -, /, <, <=, <>, =, >, ><, >=, AND, EQ, GE, GT, 
               IN, LE, LT, MAX, MIN, NE, NG, NL, NOTIN, OR, ^=, |, ||, ~=.  
 
 73         run;
 
 NOTE: The SAS System stopped processing this step because of errors.

If you are here to learn the most simple basics of SAS, I recommend to work through the free online Programming 1 course first.

[dexcort2020]

 summary data=have nway;
    member plancategory;
    var memberct cost;
    output =num sum=memberct totalcost;
run;

[Kurt_Bremser]

Are you trying to play practical jokes with us, or are yoz'u just too lazy (or too incompetent) to learn the utter basics of SAS?

---

@dexcort2020 wrote:

 summary data=have nway;
    member plancategory;
    var memberct cost;
    output =num sum=memberct totalcost;
run;

---

[Kurt_Bremser]

You can integrate the creation of plancategory in the SQL:

proc sql;
create table claims as
  select 
    memberNo,
    planPayer,
    claimHeaderId,
    cpt,
    cptdescription,
    cost,
    eventDate,
    trim(memberNo)||trim(eventDate) as visitct,
    providerNPI,
    providerName,
    providerSpecialty,
    case
      when planPayer = "ddd" then "ddd2"
      when planPayer = "ggg" then "ggg2"
      else "ooo"
    end as planCategory
  from x.eventLineDetails
  where
    providerSpecialty In ('Cardiology', "Cardiovascular Disease")
    and eventDate between "2021-01-01" and "2021-12-31"
    and claimatRisk=1
    and cpt in &EMCODES
;
quit;

Also take note how indentation is used to make the code readable.

[#12]

THANK YOU!