topic Re: Trying to create a table that shows totals for each row in SAS Programming

Trying to create a table that shows totals for each row

anonymous_user — Wed, 06 Apr 2022 21:31:15 GMT

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;

Re: Trying to create a table that shows totals for each row

PaigeMiller — Wed, 06 Apr 2022 21:20:28 GMT

@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.

Re: Trying to create a table that shows totals for each row

anonymous_user — Wed, 06 Apr 2022 21:32:46 GMT

plancategory memberCt totalcost

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

Re: Trying to create a table that shows totals for each row

PaigeMiller — Wed, 06 Apr 2022 21:35:48 GMT

Ok, if I am understanding everything properly

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

Re: Trying to create a table that shows totals for each row

anonymous_user — Thu, 07 Apr 2022 04:16:24 GMT

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?

Re: Trying to create a table that shows totals for each row

Kurt_Bremser — Thu, 07 Apr 2022 06:27:49 GMT

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

Re: Trying to create a table that shows totals for each row

Kurt_Bremser — Thu, 07 Apr 2022 06:32:40 GMT

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.

Re: Trying to create a table that shows totals for each row

dexcort2020 — Thu, 07 Apr 2022 14:04:53 GMT

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

Re: Trying to create a table that shows totals for each row

Kurt_Bremser — Thu, 07 Apr 2022 15:27:58 GMT

@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.

Re: Trying to create a table that shows totals for each row

dexcort2020 — Thu, 07 Apr 2022 16:44:50 GMT

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

Re: Trying to create a table that shows totals for each row

anonymous_user — Thu, 07 Apr 2022 17:09:20 GMT

THANK YOU!

Re: Trying to create a table that shows totals for each row

Kurt_Bremser — Thu, 07 Apr 2022 19:04:54 GMT

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;