Help using Base SAS procedures

Pivot table percentages

Reply
Super Contributor
Posts: 578

Pivot table percentages

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;

TableType1Type2Type3Type4Type5Total
Type1321113
Type2221102
Type3112202
Type4112213
Type5100122
Total322326

Any suggestions on where to start?

SAS Super FREQ
Posts: 8,864

Re: Pivot table percentages

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

Super Contributor
Posts: 578

Re: Pivot table percentages

Posted in reply to Cynthia_sas

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"

Super User
Posts: 19,772

Re: Pivot table percentages

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?

Super Contributor
Posts: 578

Re: Pivot table percentages

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

SAS Super FREQ
Posts: 8,864

Re: Pivot table percentages

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

Super Contributor
Posts: 578

Re: Pivot table percentages

Posted in reply to Cynthia_sas

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.

Frequent Contributor
Posts: 97

Re: Pivot table percentages

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

TableType1Type2Type3Type4Type5Total
Type1321103

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


.

Super Contributor
Posts: 578

Re: Pivot table percentages

Posted in reply to allurai0412

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

Super User
Posts: 19,772

Re: Pivot table percentages

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

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;

Respected Advisor
Posts: 3,156

Re: Pivot table percentages

,

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

Ask a Question
Discussion stats
  • 10 replies
  • 397 views
  • 7 likes
  • 5 in conversation