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.
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;
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;
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
;
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;
"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?
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.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.