GROUP and WHERE statements

[This widget could not be displayed.]

I am trying to get the cost to sum by id and date so that the negative dollars aren't showing in the data.  For ex, it's showing as:

  id         cost

741    $-241.00

741    $241.00

741    $241.00

This is my program:

23 proc sql;
24 create table claims
25 as select
26 memberNo,
27 planPayer,
28 claimHeaderId,
29 rollupeventid,
30 cpt,
31 cptdescription,
32 icd,
33 icdDescription,
34 sum(cost) as sumcost,
35 eventDate,
36 eventid,
37 providerNPI,
38 providerName,
39 providerSpecialty
40 from xx.eventLineDetails
41 where providerSpecialty = 'Dermatology'
42 and eventDate between "2020-12-01" and "2021-12-31"
43 and claimatRisk=1
44 and planPayer not in ('xx')
45 and cpt like ('J%')
46 and group by rollupeventid, eventDate;
__
22
76
ERROR 22-322: Syntax error, expecting one of the following: ;, !, !!, &, (, *, **, +, -, '.', /, <, <=, <>, =, >, >=, ?, AND,
BETWEEN, CONTAINS, EQ, EQT, EXCEPT, GE, GET, GROUP, GT, GTT, HAVING, IN, INTERSECT, IS, LE, LET, LIKE, LT, LTT, NE,
NET, NOT, NOTIN, OR, ORDER, OUTER, UNION, ^, ^=, |, ||, ~, ~=.

ERROR 76-322: Syntax error, statement will be ignored.

[japelin]

try fixing below.

delete "and" before "group by".

group by rollupeventid, eventDate;

[This widget could not be displayed.]

I'm now getting this Note at the bottom: NOTE: The query requires remerging summary statistics back with the original data.

also, it doesn't look like the sum of cost is accurate because it just lists the positive dollars multiple times but it did remove the negative dollars:

23 proc sql;
24 create table claims
25 as select
26 memberNo,
27 planPayer,
28 claimHeaderId,
29 rollupeventid,
30 cpt,
31 cptdescription,
32 icd,
33 icdDescription,
34 sum(cost) as sumcost,
35 eventDate,
36 eventid,
37 providerNPI,
38 providerName,
39 providerSpecialty
40 from xx.eventLineDetails
41 where providerSpecialty = 'Dermatology'
42 and eventDate between "2020-12-01" and "2021-12-31"
43 and claimatRisk=1
44 and planPayer not in ('C')
45 and cpt like ('J%')
46 group by rollupeventid, eventDate;
NOTE: The query requires remerging summary statistics back with the original data.
NOTE: Table WORK.CLAIMS created, with 1523 rows and 14 columns.

47 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 3.53 seconds
cpu time 0.13 seconds

[japelin]

This message is displayed because you are using the sum() function, which is normal
"The query requires remerging summary statistics back with the original data."

And which code remove the negative dollars?

Also, when writing code, please paste the code itself in the edit window as "SAS code" and post it, not the code output in the log.

[This widget could not be displayed.]

I'm trying to figure out how to sort it so that it is showing the sum of costs per memberno, per eventDate.

Would I have to create another data step?

[japelin]

why don't you use "order by".

[This widget could not be displayed.]

Do you know how I can sum the cost per memberno, per eventdate??

[japelin]

Why not use these code instead?

group by memberno, eventdate
order by memberno, eventdate

[Kurt_Bremser]

---

@anonymous_user wrote:
I'm trying to figure out how to sort it so that it is showing the sum of costs per memberno, per eventDate.

Would I have to create another data step?

---

That is a very simple SQL query:

proc sql;
create table claims as
  select
    memberno,
    eventdate,
    sum(cost) as sumcost
  from xx.eventlinedetails
  group by memberno, eventdate
;
quit;

but SAS provides procedures for such tasks:

proc means data=xx.eventlinedetails nway;
class memberno eventdate;
var cost;
output
  out=claims
  sum(cost)=sumcost
;
run;

[This widget could not be displayed.]

I tried the bottom one and the negative amounts came back into the data:

proc means data=claims nway;
class memberNo eventDate claimHeaderId cpt cptdescription icd icdDescription;
var cost;
output
  out=claims2
  sum(cost)=sumcost
;
run;

This is the output (but the negative amounts should be gone):

memberNo	eventDate	claimHeaderId	cpt	cptDescription	icd	icdDescription	_TYPE_	_FREQ_	sumcost
123	2021-06-30	111	Jxxxx	xxxx	xxxx	xxxxx	127	1	-$2.06
456	2020-12-30	333	Jxxxx	xxxx	xxx	xxxxx	127	1	-$1.86

[Kurt_Bremser]

If you want to filter negative results, use a WHERE= dataset option on the output. If you want to filter negative values, do so on the input dataset.