topic Re: SUM by ID and date in SAS Programming

SUM by ID and date

anonymous_user — Wed, 30 Mar 2022 06:42:08 GMT

I'm trying to get rid of negative dollar amounts in my data. For ex:

id cost eventDate

741 -$241.00 2021-10-21

741 $241.00 2021-10-21

741 $241.00 2021-11-20

I want to sum by ID and by date so that it looks like this:

id cost eventDate

741 $0 2021-10-21

741 $241.00 2021-11-20

proc sql;
create table claims
as select
memberNo,
planPayer,
claimHeaderId,
rollupeventid,
cpt,
cptdescription,
icd,
icdDescription,
cost,
eventDate,
eventid,
providerNPI,
providerName,
providerSpecialty
from xx.eventLineDetails
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%');
quit;

Re: SUM by ID and date

PeterClemmensen — Wed, 30 Mar 2022 06:44:12 GMT

Do you simply want to delete observations with a begative dollar amount? You example seems more like you want to sum the amounts grouped by ID and EventDate?

Please be more specific.

Re: SUM by ID and date

anonymous_user — Wed, 30 Mar 2022 06:48:38 GMT

Yes, sum by id and eventDate.

Re: SUM by ID and date

PeterClemmensen — Wed, 30 Mar 2022 06:51:34 GMT

Then do

data have;
input id cost :dollar8.2 eventDate :yymmdd10.;
format cost dollar8.2 eventDate yymmdd10.;
datalines;
741 -$241.00 2021-10-21
741  $241.00 2021-10-21
741  $241.00 2021-11-20
;

proc summary data = have nway;
   class id eventDate;
   var cost;
   output out = want(drop = _:) sum =;
run;

Re: SUM by ID and date

Kurt_Bremser — Wed, 30 Mar 2022 06:52:38 GMT

For building a sum, see my answer here:

https://communities.sas.com/t5/SAS-Programming/GROUP-and-WHERE-statements/m-p/804964/highlight/true#M317048

But what are the many other variables in your SQL query? To really help you, we need example data (post it as a data step with datalines into a code box, do not skip this!!) and what you expect as a result out of that.

If you want to do a sum in SQL, all variables in the select must be

part of the GROUP BY clause
or the result of a SQL summary function

or you will get a "remerge", where the original observations are all kept and each receives the sum over the group.

So it is important to know the roles of all your other variables. If, say, they are constant for a memberno and/or eventdate, then you need to include them all in the GROUP BY. If not, you need to decide which values for a given memberno/eventdate should take precedence in the result.

Re: SUM by ID and date

anonymous_user — Wed, 30 Mar 2022 08:26:38 GMT

This did get rid of the negative dollar amounts, but if I wanted to add other fields like below, the negative dollar amounts come back. Is there a way to remove those negatives with these extra fields added?

proc summary data = claims nway;
class memberNo eventDate claimHeaderId cpt cptDescription icd icdDescription;
var cost;
output out = claims2(drop = _:) sum =;
run;

Re: SUM by ID and date

Astounding — Wed, 30 Mar 2022 09:15:09 GMT

Calculate your own analysis variable. Instead of COST, use

max(cost, 0) as poscost

Re: SUM by ID and date

Kurt_Bremser — Wed, 30 Mar 2022 09:27:04 GMT

Example data.