Hi all,
I have a dataset named All_Letters, which includes all the accounts from a table called Book_On_Accounts and linked accounts of Book_On_Accounts. All_Letters include the number of letters sent to these accounts. Now, I want to see the total number of letters sent to each icustomerid from the Book_On_Accounts table (but this should not include the linked accounts).
Sample dataset:
Data All_Letters ;
infile cards expandtabs;
input icustomerid debt_code tcfPostCode$ cpostcode$ LIMA_result$ cchannel$ Num_of_letters lettered ;
datalines ;
125 1985128 BB26PL . LAS . 1 0
125 4210751 BB26PL . LAS . 1 0
724 100567 CV13BE . LAS . 1 0
724 1511593 CV13BE . LAS . 1 0
724 1899565 CV13BE . LAS . 1 0
724 2425849 CV13BE . LAS . 1 0
724 2692525 CV13BE . LAS . 1 0
724 2854302 CV13BE . LAS . 1 0
724 2942242 CV13BE . LAS . 1 0
724 4029889 CV13BE . LAS . 1 0
724 4226817 CV13BE . LAS . 1 0
782 1119416 SE29PN . LAS . 1 0
782 1710691 SE29PN . LAS . 1 0
782 2112852 SE29PN . LAS . 1 0
782 3024364 SE29PN . LAS . 1 0
;
run;
Proc sql;
create table Groupby_customer as
select a.*,
b.tcfPostCode,
b.cpostcode,
b.LIMA_result,
b.cchannel,
sum(b.Num_of_letters) as Num_of_letters,
b.lettered
from Book_On_Accounts as a
inner join work.All_Letters as b on a.icustomerid = b.icustomerid
group by a.icustomerid;
quit;
My code is counting the num_of_letters but not grouping by icustomerid. Please suggest.
Since your SELECT contains columns which are neither part of the GROUP BY nor a result of a SQL summary function, SAS will do an automatic remerge (and show a corresponding NOTE in the log).
To get yourvsummary by icustomerid, you need to do this:
proc sql;
create table groupby_customer as
select
a.icustomerid,
sum(b.num_of_letters) as num_of_letters
from book_on_accounts as a
inner join work.all_letters as b
on a.icustomerid = b.icustomerid
group by a.icustomerid
;
quit;
You asked PROC SQL to generate the SUM() of the Num_of_letters variable and then remerge it back onto all of the detail records. Is that what you wanted or not?
Since you use A.* in the select list if the dataset referenced by A already has a variable named Num_of_letters then you cannot add another variable with the same name so your SUM() will "disappear" since there is no place for SQL to put it. Try giving it a different name.
@Sandeep77 wrote:
My code is counting the num_of_letters but not grouping by icustomerid. Please suggest.
As always suggest that you show us the LOG from running the code, with the code and all notes and messages.
You show us ONE data set and nothing of the second. So we don't have complete example of what goes on. You need to provide a similar example for Book_on_Accounts and run your code against the EXAMPLE data that you provide to make sure that it has the same behavior as your problem.
Note that when you group by ONE variable and have multiple other variables not involved with summary functions the result may have issues because of the behavior of those other variables.
Since your SELECT contains columns which are neither part of the GROUP BY nor a result of a SQL summary function, SAS will do an automatic remerge (and show a corresponding NOTE in the log).
To get yourvsummary by icustomerid, you need to do this:
proc sql;
create table groupby_customer as
select
a.icustomerid,
sum(b.num_of_letters) as num_of_letters
from book_on_accounts as a
inner join work.all_letters as b
on a.icustomerid = b.icustomerid
group by a.icustomerid
;
quit;
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!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.