Grouping the data by customer is not working

[Sandeep77]

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.

[Tom]

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]

from Book_On_Accounts as a

In the Book_On_Accounts table, there is no num_of_letters column.
Table 1 (Book_On_Accounts), Table 2 (Linked_accounts)(This includes the linked accounts of Book_On_Accounts), Table 3 (All_letters) (This table counts the number of letters sent to all the bookon accounts and it's linked accounts. Now I am trying to sum the total number of letters sent from All_letters (Book_On_Accounts + Linked_accounts) by icustomerid to bring it back to the Book_On_Accounts . For e.g icustomerid 125 should give output as 2 in the num_of_letters but it should show only one row as it is just one customer.

[ballardw]

---

@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.

[Kurt_Bremser · Accepted solution]

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;

[Sandeep77]

Thank you!!