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

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

nikita2 — Wed, 19 Jul 2017 21:04:33 GMT

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!

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

SASKiwi — Thu, 20 Jul 2017 07:12:50 GMT

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?

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

nikita2 — Thu, 20 Jul 2017 15:46:21 GMT

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!

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

SASKiwi — Sat, 22 Jul 2017 23:55:41 GMT

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.