topic Re: GROUP and WHERE statements in SAS Programming

GROUP and WHERE statements

anonymous_user — Wed, 30 Mar 2022 04:01:12 GMT

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

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.

Re: GROUP and WHERE statements

japelin — Wed, 30 Mar 2022 04:32:35 GMT

try fixing below.

delete "and" before "group by".

group by rollupeventid, eventDate;

Re: GROUP and WHERE statements

anonymous_user — Wed, 30 Mar 2022 04:53:35 GMT

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

Re: GROUP and WHERE statements

japelin — Wed, 30 Mar 2022 05:05:05 GMT

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.

Re: GROUP and WHERE statements

anonymous_user — Wed, 30 Mar 2022 05:14:00 GMT

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?

Re: GROUP and WHERE statements

japelin — Wed, 30 Mar 2022 05:22:31 GMT

why don't you use "order by".

Re: GROUP and WHERE statements

anonymous_user — Wed, 30 Mar 2022 05:27:06 GMT

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

Re: GROUP and WHERE statements

japelin — Wed, 30 Mar 2022 05:36:21 GMT

Why not use these code instead?

group by memberno, eventdate
order by memberno, eventdate

Re: GROUP and WHERE statements

Kurt_Bremser — Wed, 30 Mar 2022 06:39:14 GMT

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

Re: GROUP and WHERE statements

anonymous_user — Wed, 30 Mar 2022 08:50:48 GMT

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

Re: GROUP and WHERE statements

Kurt_Bremser — Wed, 30 Mar 2022 09:25:29 GMT

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.