Exploring, modeling, predicting and reporting with SAS Visual Analytics and SAS Visual Statistics

How do I create non-categorized cross tab in SAS VA 7.1

Reply
New Contributor
Posts: 2

How do I create non-categorized cross tab in SAS VA 7.1

Hi,

 

I want to create a non-hierarchical/non-categorized/ cross tab in SAS Visual Analytics 7.1 which would look something like the screenshot attached. I am trying to create such a table but it splits up columns which is not a desirable display for my business purpose. I would highly appreciate any leads in this regard.

 

Thanks!


doubt1.PNG
Super User
Posts: 3,105

Re: How do I create non-categorized cross tab in SAS VA 7.1

What does your input data look like? Please explain what a cell containing the value 7 with a column label of Mango and a row label of Apple actually mean? Are we missing another data dimension? 

New Contributor
Posts: 2

Re: How do I create non-categorized cross tab in SAS VA 7.1

I have transactional data in which I want to see shopping patterns of customers. In the data, for each customer, I have a binary column for each of the items- Apple, Mango, Banana, Orange. I am trying to visualize co-occurence matrix to understand how many customers bought Apple and Mango together, how many purchased Apples, Mangoes and so on. I am interested to see counts across these combinations and individual items overall for my data. Attached is the screenshot of how my data looks like.

 

Thank you so much!


doubt2.PNG
Super User
Posts: 3,105

Re: How do I create non-categorized cross tab in SAS VA 7.1

OK, so you are trying to do market basket analysis. I think you will need to transform your data like this:

 

data fruit;
  input customer 
        apple
        banana
        mango
        orange
        ;
datalines;
1         1        1        0        0
2         1        0        1        1
3         0        0        1        1
4         0        1        1        0
5         1        1        1        1
;
run;

data fruit2;
  keep customer fruit1 fruit2 count;
  set fruit;
  array fruits (*) apple banana mango orange;
  do i = 1 to dim(fruits);
    if fruits(i) = 1 then do j = i to dim(fruits);
      if fruits(j) = 1 then do;
        fruit1 = vname(fruits(i));
        fruit2 = vname(fruits(j));
        count = 1;
        output;
      end;
    end;
  end;
run;

Then you can try a cross tab on the columns fruit1 and fruit2.

Ask a Question
Discussion stats
  • 3 replies
  • 217 views
  • 0 likes
  • 2 in conversation