Help using Base SAS procedures

What's the best SAS procedure and how to use to get the report in the required tabulated format?

Reply
Contributor
Posts: 46

What's the best SAS procedure and how to use to get the report in the required tabulated format?

Hi, I'm trying to generate a market product penetration report to see what customers in my data who principally buy a product and also buy other products along with it. For example, below is report I want to generate and I want to establish the item penetration of a customer. In the given example, out of 20 customers who bought beer, 5 bought crisps, 3 bought fries, 1 bought soda, 1 bought mineral water and 1 bought fast food. Can anybody please suggest me a procedure or a way to generate this kind of a report?

  Customers Beer Crisps Fries Soda Mineral Water Fast food
Beer 20   5 3 1 1 1
Crisps 35 5   1 8 7 4
Fries 32 9 2   20 2 15
Soda 50 15 8 5   2 20
Mineral Water 40 1 10 3 5   6
Fast food 30 5 18 12 24 4  
PROC Star
Posts: 1,558

Re: What's the best SAS procedure and how to use to get the report in the required tabulated format?

Please show what the input data looks like.

Contributor
Posts: 46

Re: What's the best SAS procedure and how to use to get the report in the required tabulated format?

Hi, Please ignore my previous post as it was not explained properly, here are the details:

Here is my input data:

Product_code Product_name Allied_product Customer_penetration
1 Beer Crisps 8872
1 Beer Fries 337
1 Beer Soda 25503
1 Beer Mineral water 56915
2 Crisps Fries 599
2 Crisps Soda 25230
2 Crisps Mineral water 24801
3 Fries Soda 983
3 Fries Mineral water 1244
4 Soda Mineral water 98661

 

And here is my WANTED OUTPUT:

Product_code Product_Name Beer Crisps Fries Soda Mineral_water
1 Beer   8872 337 25503 56915
2 Crisps 8872   599 25230 24801
3 Fries 337 599   983 1244
4 Soda 25503 25230 983   98661

 

Can anybody help?

Valued Guide
Posts: 765

Re: What's the best SAS procedure and how to use to get the report in the required tabulated format?

[ Edited ]

Hi, PROC TRANSPOSE gets you close, but TRANSPOSE will only put each value of the variable "Customer_penetration" into one cell. There are most likely shorter ways, but this works (shortened the variable names, I can't type) ...


data x;
input pc pn :$15. ap &$15. cp;
datalines;
1 Beer Crisps    8872
1 Beer Fries     337
1 Beer Soda     25503
1 Beer Mineral water    56915
2 Crisps Fries    599
2 Crisps Soda    25230
2 Crisps Mineral water    24801
3 Fries Soda    983
3 Fries Mineral water    1244
4 Soda Mineral water    98661
;

 

data y (keep=product_: BEER CRISPS FRIES SODA MINERAL_WATER);
array y(5,5) _temporary_;
array p(5) $15 ('BEER' 'CRISPS' 'FRIES' 'SODA' 'MINERAL WATER');

array c(5) BEER CRISPS FRIES SODA MINERAL_WATER;
set x end=last;
i = whichc(upcase(pn), of p(*)); j = whichc(upcase(ap), of p(*));
y(i,j) = cp; y(j,i) = cp;
if last then
   do product_number=1 to 4;
      product_name = p(product_number);
   do j=1 to 5;
      c(j) = y(product_number,j);
   end;
   output;
   end;
run;


data set Y ...
product_    product_                                         MINERAL_
 number       name       BEER    CRISPS    FRIES     SODA      WATER

    1        BEER           .      8872     337     25503      56915
    2        CRISPS      8872         .     599     25230      24801
    3        FRIES        337       599       .       983       1244
    4        SODA       25503     25230     983         .      98661

 

PROC TRANSPOSE gets you close if you muck around with your data ...


data xx (drop=zz);
set x;
output;
zz = ap; ap = pn; pn = zz;
output;
run;

 

proc sort data=xx;
by pn;
run;

 

proc transpose data=xx out=yy (drop=_name_);
by pn;
id ap;
var cp;
run;

 

data set YY ...

                                                     Mineral_

Obs    pn               Crisps    Fries     Soda      water      Beer

 1     Beer               8872      337    25503      56915         .
 2     Crisps                .      599    25230      24801      8872
 3     Fries               599        .      983       1244       337
 4     Mineral water     24801     1244    98661          .     56915
 5     Soda              25230      983        .      98661     25503

PROC Star
Posts: 1,558

Re: What's the best SAS procedure and how to use to get the report in the required tabulated format?

[ Edited ]

Like this?

 

data HAVE(index=(PN));
  input PC PN :$15. AP &$15. CP;
  cards; 
  1 Beer Crisps    8872 
  1 Beer Fries     337 
  1 Beer Soda     25503 
  1 Beer Mineral water    56915 
  2 Crisps Fries    599 
  2 Crisps Soda    25230 
  2 Crisps Mineral water    24801 
  3 Fries Soda    983 
  3 Fries Mineral water    1244 
  4 Soda Mineral water    98661 
run;
data X;
  set HAVE
      HAVE(rename=(AP=PN PN=AP));            %* get 2nd set of data for matrix bottom;
  set HAVE(keep=PN PC) key=PN;               %* get correct PC ;
  if _IORC_ then do; _ERROR_=0; delete; end; %* delete if no PC;
run;
proc tabulate;
 class PC PN AP;
 var CP;
 table PC=''*PN='', AP=''*CP=''*sum=''*f=8.0;
run;

 

 

 

  BeerCrispsFriesMineral waterSoda
1Beer.88723375691525503
2Crisps8872.5992480125230
3Fries337599.1244983
4Soda255032523098398661.

 

Ironic than pasting from the SAS output window yields:

 

Your post has been changed because invalid HTML was found in the message body. The invalid HTML has been removed. Please review the message and submit the message when you are satisfied.

 

 

 

Valued Guide
Posts: 765

Re: What's the best SAS procedure and how to use to get the report in the required tabulated format?

Neat ideas ... index, have+have.

Ask a Question
Discussion stats
  • 5 replies
  • 351 views
  • 0 likes
  • 3 in conversation