turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- SAS Procedures
- /
- Pivot table percentages

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

02-14-2013 11:38 AM

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?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

02-14-2013 12:31 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

02-14-2013 02:45 PM

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"

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

02-14-2013 12:55 PM

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?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

02-14-2013 02:45 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

02-14-2013 09:06 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

02-19-2013 08:33 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

02-14-2013 11:43 PM

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**

.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

02-19-2013 08:37 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

02-15-2013 10:38 AM

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;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

02-15-2013 01:59 PM

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;

end;

Total=type(j);

tot(j)=type(j);

output;

end;

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