>if a customer purchase at company A, how much ... they spend there.
This is not what you are showing:
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.
data T; * sample data;
input R CID COMPANY $1. 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
data FINAL; * prepare output table;
sasfile T load; * load data in memory;
proc sort data=T(keep=COMPANY) out=COLIST nodupkey; * get list of companies;
%do %while(&rc=0); * loop thru companies;
proc sql; * derive figures for 1 company;
create table CO_&coname as
select sum(AMT) as &coname ,COMPANY
where CID in (select unique CID from T where COMPANY="&coname")
group by COMPANY;
proc transpose data=CO_&coname out=CO_&coname; * put figures in 1 row;
id COMPANY ;
data FINAL; * add to output table;
set FINAL CO_&coname;
sasfile T close; * unload data from memory;