Summing problem

[psquares]

Hi I been stuck on this problem for a while now and hoping someone could
shed a light how to program this in an efficient manner my data set is
approx 41 million records.


HAVE:

Row(R) CID COMPANY Amt
1 111 A 4
2 111 D 2
3 111 G 3
4 111 X 4
5 112 A 6
6 112 D 2
7 112 G 8
8 113 G 4
9 113 X 2
10 114 A 5
11 115 X 5


WANT:


A D G X
A 15 4 11 4
D 10 4 11 4
G 10 4 15 6
X 4 2 7 11


With the sum is breakdown as follow


A D G X
A 4+6+5(R1+R5+R10) 2+2(R2+R6) 3+8 (R3+R7) 4(R4)
D 4+6(R1+R5) 2+2(R2+R6) 3+8 (R3+R7) 4(R4)
G 4+6(R1+R5) 2+2(R2+R6) 3+8+4(R3+R7+R8) 4+2(R4+R9)
X 4(R1) 4(R2) 3+4(R3+R8) 4+2+5(R4+R9+R11)

So essentially what I need is if a customer purchase at company A, how
much is the total amount they spend there.

And we also want to know how much they are spending elsewhere, so for
example for the first row, all customers spending at company A how much
they are spending at company D, G & X.

Many Thanks in advance.

[ChrisNZ]

>if a customer purchase at company A, how much ... they spend there.

This is not what you are showing:
A 4+6+5(R1+R5+R10)
is for all customers.

In any case, here is how to obtain the same numbers you are showing.
Efficient is not how I would describe it, but it works.
Loading the table in memory speeds things up when making the successive passes (You might want to drop unnecessary columns if you have many).
If the amount of memory you have is too small, the code will still work, but an index on CID and maybe another on COMPANY will help speed.
[pre]
data T; * sample data;
input R CID COMPANY $1. AMT ;
cards;
1 111 A 4
2 111 D 2
3 111 G 3
4 111 X 4
5 112 A 6
6 112 D 2
7 112 G 8
8 113 G 4
9 113 X 2
10 114 A 5
11 115 X 5
run;
data FINAL; * prepare output table;
delete;
run;
sasfile T load; * load data in memory;
proc sort data=T(keep=COMPANY) out=COLIST nodupkey; * get list of companies;
by COMPANY;
run;
%macro t;
%let dsid=%sysfunc(open(COLIST));
%let rc=%sysfunc(fetch(&dsid));
%do %while(&rc=0); * loop thru companies;
%let coname=%sysfunc(getvarc(&dsid,1));
proc sql; * derive figures for 1 company;
create table CO_&coname as
select sum(AMT) as &coname ,COMPANY
from T
where CID in (select unique CID from T where COMPANY="&coname")
group by COMPANY;
quit;
proc transpose data=CO_&coname out=CO_&coname; * put figures in 1 row;
id COMPANY ;
run;
data FINAL; * add to output table;
set FINAL CO_&coname;
run;
%let rc=%sysfunc(fetch(&dsid));
%end;
%let rc=%sysfunc(close(&dsid));
%mend;%t
sasfile T close; * unload data from memory;

[psquares]

Thank you Chris Message was edited by: psquares