Resume count matrix

[osmelbrito]

Hello communities people

I have this table, where each row is a customer and each colum BUn represent that id this customer has active products on that business unit (in this example I use a text, but we can also use a binary flag)

CUST	BU1	BU2	BU3	BU4
123	SEG	AFI
124		AFI	ARS
125	SEG
126		AFI
127			ARS
128				FID

I'm tryng to buid a count matrix to try to get this result

	BU1	BU2	BU3	BU4
BU1	2	1	0	1
BU2	1	3	1	0
BU3	0	1	2	0
BU4	0	0	0	1

where each cell is a frequency (count) of customers that are commons between business units.

I was trying to do this report using PROC TABULATE, but I did not get the result

PROC TABULATE
DATA=WORK.W_DATOS_CLIENTE2
	
	;
	
	VAR CLIENTE_ID;
	CLASS FILIAL1 /	ORDER=UNFORMATTED MISSING;
	CLASS FILIAL2 /	ORDER=UNFORMATTED MISSING;
	CLASS FILIAL3 /	ORDER=UNFORMATTED MISSING;
	CLASS FILIAL4 /	ORDER=UNFORMATTED MISSING;
	TABLE 
		/* ROW Statement */
		FILIAL1 FILIAL2 FILIAL3 FILIAL4 ,
		/* COLUMN Statement */
		FILIAL1 *(CLIENTE_ID * N=' ' )FILIAL2 *(CLIENTE_ID * N=' ' )FILIAL3 *(CLIENTE_ID * N=' ' )FILIAL4 *(CLIENTE_ID * N=' ' ) 		;
	;

RUN;

[Reeza · Accepted solution]

I don't think BU1/BU4 column is correct? It should be 0?

Here's a trick using PROC CORR

data have;
infile cards dsd truncover;
informat CUST BU1-BU4 $8.;
input CUST	BU1-BU4 ;
cards;
123,SEG,AFI,,
124,,AFI,ARS,
125,SEG,,,
126,,AFI,,
127,,,ARS,
128,,,,FID
;;;;

run;

data convert2binary;
set have;
array bu(4) bu1-bu4;
array _bu(4) _bu1-_bu4;
do i=1 to dim(bu);
if not missing(bu(i)) then _bu(i) = 1;
else _bu(i) = 0;
end;
run;

ods select none;
ods output sscp=coocs;
proc corr data=convert2binary sscp;
var _bu1-_bu4;
run;
ods select all;

proc print data=coocs;
run;

[osmelbrito]

thanks

That's what I was trying to do.

But why do you use proc corr? As I understand your code, you use sscp option in order to show only sum of squares

[Reeza]

---

@osmelbrito wrote:

But why do you use proc corr? As I understand your code, you use sscp option in order to show only sum of squares

---

Mathematically it works out to be the same thing, so let the SAS process that's optimized to calculate this, do it that way. 

[Ksharp]

data have;
infile cards dsd truncover;
informat CUST BU1-BU4 $8.;
input CUST BU1-BU4 ;
cards;
123,SEG,AFI,,
124,,AFI,ARS,
125,SEG,,,
126,,AFI,,
127,,,ARS,
128,,,,FID
;;;;
run;
ods select none;
ods output burt=want(keep=Label SEG AFI ARS FID where=(Label is not missing));
proc corresp data=have observed short mca missing;
tables BU1-BU4;
run;
ods select all;