SUM by ID and date

[This widget could not be displayed.]

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;

[PeterClemmensen]

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.

[This widget could not be displayed.]

Yes, sum by id and eventDate.

[PeterClemmensen]

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;

[This widget could not be displayed.]

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;

[Kurt_Bremser]

Example data.

Example data.

Example data.

[Kurt_Bremser]

For building a sum, see my answer here:

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

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.

[Astounding]

Calculate your own analysis variable. Instead of COST, use

max(cost, 0) as poscost