how to filter a transaction dataset of Association Rule?

[priscillatsea]

Dear all,

I am doing association rule analysis for my project. And I would like to separate the dataset into Monday, Tuesday....etc to see the pattern (The variable is named TOT, and 1=monday, 2=tueday.........). I try to apply filter, but it is only for raw or train dataset.

What can I filter a transaction dataset? I can only use SAS code to do that?

Thank you so much.

Regards,

Priscilla

[AnnaBrown]

Hi @priscillatsea,

Thanks for your question! I wanted to let you know that a SAS expert is looking into this and will get back to you soon.

Best,

Anna

[TWoodfield]

I will start with a disclaimer: I love the Enterprise Miner SAS Code node. With the SAS Code node, all things are possible.

From the wording of your question, I am assuming you would prefer a code free solution. I do not think that is possible.

For my explanation, I have a hypothetical transaction data set called NFS_Assoc. It has an ID variable called Basket and a target variable called ProductName. There is also a DayOfWeek variable with values 1=Sunday through 7=Saturday. While your coding is 1=Monday, etc., the same strategy will apply. There is also a CustomerID variable and a Date variable.

I will provide a minimal code/maximal pain solution first.

To do an association analysis for Wednesday transactions, proceed as follows.

1. Create a data source for the NFS_Assoc transaction table. The data role is “TRANSACTION”. Basket has role=ID and level=nominal. ProductName has role=Target and level=nominal. DayOfWeek has role=Input and level=interval.
2. Create a diagram. I’ll call it Association.
3. Drag the transaction table into the diagram.
4. Change the role of the transaction table to raw. In the property panel, Train=>Role=>Raw.
5. Attach a Filter node from the Sample tab. Change the default filtering methods to none. Select the Interval Variables property to open the Interactive Interval Filter wizard. Set the lower limit for DayOfWeek to 3.5 and the upper limit to 4.5, which will capture 4=Wednesday for my data. Run the node.
6. Attach a SAS code node from the Utility tab to the Filter node. Enter the following code. 

   data &EM_EXPORT_TRANSACTION;
      set &EM_IMPORT_DATA;
   run;
   

    
7. Save the code and run the node. This is the only way I know how to change RAW data to TRANSACTION data in a process flow. There may be another code free method, but I don’t know it.
8. Attach a Metadata node from the Utility tab. The SAS code node loses all the metadata when using tables with different roles. This means the basic advisor assigns new roles, and these new roles are wrong. Select the Transactions property under the Variables section of the property panel, and set the roles and levels as before. Run the node.
9. Attach an Association node from the Explore tab, run the node, and view the results for your analysis of Wednesday market baskets.
10. Repeat for the other days. You will have seven process flows for a seven day week, or five process flows for weekday days.

For the minimal code/maximal pain solution, you needed a process flow with five nodes. For the maximal code/minimal pain solution, you only need three nodes.

1. Drag the transaction table into the diagram.
2. Attach a SAS code node to the transaction table. Enter the following code. 

   data &EM_EXPORT_TRANSACTION;
      set &EM_IMPORT_TRANSACTION;
      where DayOfWeek=4;
   run;
   

3. Save the code and run the node.
4. Attach an Association node. Run the node. View the results for Wednesday.
5. Repeat for other days.

You can see why I prefer the more complicated coding solution (one extra line of code) to the kludging with extra nodes solution.

You indicate that your day of week variable is TOT, so everywhere I refer to DayOfWeek, you should substitute TOT, and you should use your coding (1=Monday, etc.).

You made me think about more efficient ways to pass through all DayOfWeek values, rather than just copying and pasting process flows. Perhaps Start Group/End Group would work. I don’t know how to do this. Perhaps someone has a better idea.