topic Re: Resume count matrix in SAS Programming

Resume count matrix

osmelbrito — Mon, 22 Aug 2022 15:43:35 GMT

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;

Re: Resume count matrix

Reeza — Mon, 22 Aug 2022 16:20:23 GMT

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;

Re: Resume count matrix

osmelbrito — Mon, 22 Aug 2022 16:45:26 GMT

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

Re: Resume count matrix

Reeza — Mon, 22 Aug 2022 16:55:57 GMT

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

Re: Resume count matrix

Ksharp — Tue, 23 Aug 2022 12:19:49 GMT

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;