topic Re: Pivot table percentages in SAS Procedures

Pivot table percentages

DBailey — Thu, 14 Feb 2013 16:38:39 GMT

Here's a sample of my data which describes several different types of accounts a customer has:

data have;

input id Type1 Type2 Type3 Type4 Type5;

cards;

1 2 0 0 0 0

2 3 1 0 0 0

3 1 1 1 1 1

4 0 0 0 0 1

5 0 0 2 2 0

6 0 0 0 1 1

run;

Table	Type1	Type2	Type3	Type4	Type5	Total
Type1	3	2	1	1	1	3
Type2	2	2	1	1	0	2
Type3	1	1	2	2	0	2
Type4	1	1	2	2	1	3
Type5	1	0	0	1	2	2
Total	3	2	2	3	2	6

Any suggestions on where to start?

Re: Pivot table percentages

Cynthia_sas — Thu, 14 Feb 2013 17:31:24 GMT

Hi:

I don't understand where the percentages come in. It looks like you're counting...but I am confused by WHAT you are counting and why you have TYPE1-TYPE5 in both the ROWs and the COLUMNs of your output. Do you want an output REPORT, an output DATASET??? or??? SAS can only make pivot tables in a few ways, so if you want an Excel pivot table from your SAS dataset, do you have the SAS Add-in for Microsoft Office or are you using ODS TAGSETS.TABLEEDITOR to make your pivot table from an HTML file?

What code have you tried?

cynthia

Re: Pivot table percentages

Reeza — Thu, 14 Feb 2013 17:55:00 GMT

It looks like you're counting people, how many are in type 1, how many are in type 1 and type 2?

Does that sound accurate?

Re: Pivot table percentages

DBailey — Thu, 14 Feb 2013 19:45:09 GMT

Sorry for the confusion...

The percentages would be the cell value / 6.

I'm not tied to it being a pivot table, I just didn't know what else to call it. I'm trying to find an easy way to answer questions like "how many customers who have a type1 product also have a type2"

Re: Pivot table percentages

DBailey — Thu, 14 Feb 2013 19:45:44 GMT

questions like "how many customers who have type1 products also have type2"

Re: Pivot table percentages

Cynthia_sas — Fri, 15 Feb 2013 02:06:53 GMT

Again, I'm confused by your data and your desired results. How did you get 3 for the total of Type1 going down to the final row??? I see 3+2+1+1 = 7 and Type 1 going across, I see 3+2+1+1...again, you show 3 in the Total column, How did you derive 3 in either location?

cynthia

Re: Pivot table percentages

allurai0412 — Fri, 15 Feb 2013 04:43:52 GMT

hi,

i am not taking your observation values since they seems to be not useful..

input id Type1 Type2 Type3 Type4 Type5;

cards;

1 * 0 0 0 0

2 * * 0 0 0

3 * * * * *

4 0 0 0 0 *

5 0 0 * * 0

6 0 0 0 * *

run;

My interpretation for your desired ouput ...first ROW

Table	Type1	Type2	Type3	Type4	Type5	Total
Type1	3	2	1	1	0	3

number investors in type1 only ................3

number investors in type1 and type2.........2

number investors in type1 and type3.........1

number investors in type1 and type4.........1

number investors in type1 and type5.........0............(should be 1);..

why it is displaying as '0' in desired output?

Regards

ALLU

Re: Pivot table percentages

Reeza — Fri, 15 Feb 2013 15:38:32 GMT

This gets you started, but its not efficient by any means .

Also, if combinations are missing they will be missing in the transposed table. You can fix that by creating a table with all combinations and merging it if required.

data have;

input id Type1 Type2 Type3 Type4 Type5;

cards;

1 2 0 0 0 0

2 3 1 0 0 0

3 1 1 1 1 1

4 0 0 0 0 1

5 0 0 2 2 0

6 0 0 0 1 1

;

run;

data have2;

set have;

array type(5) type1-type5;

do t=1 to 5;

if type(t) >0 then output;

end;

drop type1-type5;

run;

*Generate master table;

proc sql;

create table master_blank as

select distinct t1.t, t2.t as t_cross, 0 as count

from have2 t1

cross join (select distinct t from have2) t2;

quit;

proc sql;

create table counted as

select t, t_cross, count(distinct id) as count

from fill

group by t, t_cross;

quit;

proc transpose data=counted out=counted2 prefix=type;

var count;

by t;

id t_cross;

run;

Re: Pivot table percentages

Haikuo — Fri, 15 Feb 2013 18:59:44 GMT

First, some comments. From your previous posts, I can tell you are an experienced SAS programmer, so I don't expect that your question can be addressed easily. From my limited knowledge, I believe it would be a lot easier if you have access to OLAP Cube Studio, if not, given the potential matrix calculation involved, even IML can do a easier job than just BASE SAS. The following code is only a preliminary attempt of producing the exact result you were asking.

data have;

input id Type1 Type2 Type3 Type4 Type5;

cards;

1 2 0 0 0 0

2 3 1 0 0 0

3 1 1 1 1 1

4 0 0 0 0 1

5 0 0 2 2 0

6 0 0 0 1 1

run;

proc sql;

select cats(nobs) into :idct from dictionary.tables where libname='WORK' AND MEMNAME='HAVE';

select cats(NVAR-1) into :varct from dictionary.tables where libname='WORK' AND MEMNAME='HAVE';quit;

data want;

length Table $5.;

set have end=last;

array t(&idct,&varct) _temporary_;

array tot(%eval(&varct+1)) _temporary_;

array type type1-type&varct;

do j=1 to &varct;

t(id,j)=ifn(type(j)>0,1,0);

end;

if last then do;

do j=1 to &varct;

call missing (of type(*));

table=vname(type(j));

do k=1 to &varct;

do i=1 to &idct;

type(k)=sum(type(k),t(i,j)*t(i,k));

end;

Total=type(j);

tot(j)=type(j);

output;

end;

if last and j > &varct then do;

do i=1 to &varct;

type(i)=tot(i);

end;

table='Total';

total=&idct;

output;

end;

drop id i k j;

run;

Haikuo

Re: Pivot table percentages

DBailey — Tue, 19 Feb 2013 13:33:58 GMT

Thanks for looking again. The cells are not mutually exclusive, so you can't add down rows or across columns. There are a total of 6 customers, three of whom have Type1 products. So the row total for type1 is 3 (as is the column total for type 1). There are two customers who have both Type1 products and Type2 products (intersection of Type1 and Type2 =2).

The row/column totals are the number of customers having at least one product of that type. The intersections represent the number of customers having at least of one of both types. So the value represented by (x,y) would be the number of customers having products of type x and type y.

I hope that makes more sense.

Re: Pivot table percentages

DBailey — Tue, 19 Feb 2013 13:37:30 GMT

You're right..based on the input data, there is one customer having both Type1 and Type5 products. I've updated the table.