topic Re: How to perform SUM for Distinct values in the data. in SAS Enterprise Guide

How to perform SUM for Distinct values in the data.

PrudhviB — Mon, 23 Nov 2020 18:30:05 GMT

Hi SAS Experts,

I have a data set as shown below:

Day	Record ID	Calls
Sun	abc1	10
Sun	abc1	10
Sun	abc1	10
Sun	abc2	5
Sun	abc2	5
Sun	abc2	5
Sun	abc3	12
Sun	abc4	1
Sun	abc4	1
Sun	abc4	1

and my desired output is this :

Day	Total Records on Sunday	Total calls
Sun	4	28

Total Calls is sum of distinct values for the calls which is 10+5+12+1 = 28.

I am trying to perform sum for the distinct values in a summary format. I know you can sort the data by record Id first and then perform sum, but I do have other variables that need from the data set, which restricts to follow this process.

In advance, thank you for your time.

Re: How to perform SUM for Distinct values in the data.

Kurt_Bremser — Mon, 23 Nov 2020 18:36:20 GMT

This creates your expected result:

data have;
input day $ record_id $ calls;
datalines;
Sun abc1  10
Sun abc1  10
Sun abc1  10
Sun abc2  5
Sun abc2  5
Sun abc2  5
Sun abc3  12
Sun abc4  1
Sun abc4  1
Sun abc4  1
;

proc sql;
create table want as
  select
    day,
    count(distinct record_id) as total_records,
    sum(distinct calls) as total_calls
  from have
  group by day
;
quit;

Re: How to perform SUM for Distinct values in the data.

novinosrin — Mon, 23 Nov 2020 18:50:50 GMT


data have;
input Day $	Record_ID $	Calls;
cards;
Sun	abc1	10
Sun	abc1	10
Sun	abc1	10
Sun	abc2	5
Sun	abc2	5
Sun	abc2	5
Sun	abc3	12
Sun	abc4	1
Sun	abc4	1
Sun	abc4	1
;

data want;
 set have;
 by day Record_ID notsorted;
 if first.day then call missing(Total_Records_on_Sunday,Total_calls);
 if first.Record_ID then do;
   Total_Records_on_Sunday+1;
   Total_calls+calls;
 end;
 if last.day;
 keep day Total_Records_on_Sunday Total_calls;
run;

Re: How to perform SUM for Distinct values in the data.

PrudhviB — Mon, 23 Nov 2020 19:10:31 GMT

Hi @Kurt_Bremser this solution returning same value if i have more records with same calls for example for the below code total calls are same even with the new records. data have;
input day $ record_id $ calls;
datalines;
Sun abc1 10
Sun abc1 10
Sun abc1 10
Sun abc2 5
Sun abc2 5
Sun abc2 5
Sun abc3 12
Sun abc4 1
Sun abc4 1
Sun abc4 1
Sun abc1 10
Sun abc1 10
Sun abc6 10
Sun abc6 10
Sun abc7 10
Sun abc8 10
;

Re: How to perform SUM for Distinct values in the data.

PrudhviB — Mon, 23 Nov 2020 19:14:28 GMT

this seems to work, if my day is actual date? like 10/30/2020 will this code still work ?

I am coding on EG environment, is there a better way i can do it on SAS EG ?

Re: How to perform SUM for Distinct values in the data.

novinosrin — Mon, 23 Nov 2020 19:22:05 GMT

"this seems to work, if my day is actual date? like 10/30/2020 will this code still work ?

I am coding on EG environment, is there a better way i can do it on SAS EG ?"

Hi @PrudhviB Whether your day is actual date or any other value, it shouldn't matter as long as the values can form a by group(or group by). For example, if you have 10 transactions on a given date, you would expect to have 10 records for the same date. Therefore, it should still work fine.

In EG, if you are using query builder, your best bet would be is to use a SQL solution. However SAS programming is the same regardless of the SAS client you are using, be it PC SAS, SAS EG, Studio etc.

I would suggest to please test the solution on your data and see if it really works. If it doesn't, it's garbage. And if it does, use it. Sound good?

Re: How to perform SUM for Distinct values in the data.

Kurt_Bremser — Mon, 23 Nov 2020 19:36:22 GMT

Please supply example data that reflects your real situation, and show the results you want out of that particular data.

My code solves the question as stated in the original post, it's not the magic "solve the moving goalpost problem" code.

Re: How to perform SUM for Distinct values in the data.

PrudhviB — Mon, 23 Nov 2020 20:28:47 GMT

Thank you for your response. My apologies, I should have shared the total dataset.

Re: How to perform SUM for Distinct values in the data.

PrudhviB — Mon, 23 Nov 2020 20:29:19 GMT

Thank you, it did work for my data.

Re: How to perform SUM for Distinct values in the data.

PeterClemmensen — Tue, 24 Nov 2020 08:19:11 GMT

My 2 cents

data have;
input Day $	Record_ID $	Calls;
cards;
Sun abc1 10
Sun abc1 10
Sun abc1 10
Sun abc2 5
Sun abc2 5
Sun abc2 5
Sun abc3 12
Sun abc4 1
Sun abc4 1
Sun abc4 1
Mon abc1 10
Mon abc1 10
Mon abc1 10
Mon abc2 5
Mon abc2 5
Mon abc2 5
Mon abc3 12
Mon abc4 1
Mon abc4 1
Mon abc4 1
Tue abc1 10
Tue abc1 10
Tue abc1 10
Tue abc2 5
Tue abc2 5
Tue abc2 5
Tue abc3 12
Tue abc4 1
Tue abc4 1
Tue abc4 1
;

data _null_;
   dcl hash h (ordered: "A");
   h.definekey ("Day");
   h.definedata ("Day", "rec", "tot");
   h.definedone ();

   dcl hash u ();
   u.definekey ("Day", "Calls");
   u.definedone ();

   do until (z);
      set have end = z;
      if h.find() ne 0 then call missing (rec, tot);
      if u.check() ne 0 then do;
         tot = sum (tot, Calls);
         rec = sum (rec, 1);
         u.add();
      end;
      h.replace();
   end;

   h.output (dataset: "want");
   stop;
run;