BookmarkSubscribeRSS Feed
DBailey
Lapis Lazuli | Level 10

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?

10 REPLIES 10
Cynthia_sas
SAS Super FREQ

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

DBailey
Lapis Lazuli | Level 10

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"

Reeza
Super User

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?

DBailey
Lapis Lazuli | Level 10

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

Cynthia_sas
SAS Super FREQ

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

DBailey
Lapis Lazuli | Level 10

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.

allurai0412
Fluorite | Level 6

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


.

DBailey
Lapis Lazuli | Level 10

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

Reeza
Super User

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;

Haikuo
Onyx | Level 15

,

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 1428 views
  • 7 likes
  • 5 in conversation