SAS Programming

piPy · Posted 04-01-2019 08:30 AM

Hi,

I have an over simplified data set that looks like

ID	Prod_Cat	Prod
1111	PN1	1
1111	PN2	2
1111	PN3	3
1111	QC1	4
1111	QC2	5
2222	PN1	2
2222	PN2	3
2222	PN3	4
2222	QC1	5
2222	QC2	6

And I would like to have an output data set (already sorted) that looks like the attached csv file. The actual number of products are 68 and products categories is 10. I have consulted the similar topics on sas community forum but none does what I'm after. Much appreciated for any assistance or pointers how I can get to this final result.

Thank you

ballardw · Posted 04-01-2019 10:55 AM

Have you calculated how many records you are going to generate? How long the longest data line is going to be?

If you are looking at doing 68 elements grouped 1 at a time, then 2, then 3 and so on up to 68 then you are looking at roughly 2.9E20 combinations.

You can look at the functions ALLCOMB, ALLPERM or CALL ALLCOMB and CALL ALLPERM for the tools needed but your current description is going to make lines of a file that is extremely large.

And how are you going to use a poorly designed structure with multiple values within a single field to begin with.

For those not interested in downloading the file the example looks like:

ID,Prod_Cat_Comb,Prod_Combinations,Prod_Cat_Perm,Prod_Permutations
1111,PN1,1,"PN1,PN2,PN3,QC1,QC2","1,2,3,4,5"
1111,PN2,2,"PN2,PN1,PN3,QC1,QC2","2,1,3,4,5"
1111,PN3,3,"PN3,PN1,PN2,QC1,QC2","3,1,2,4,5"
1111,QC4,4,"PN1,PN3,PN2,QC1,QC2","1,3,2,4,5"
1111,QC5,5,"PN2,PN3,PN1,QC1,QC2","2,3,1,4,5"
1111,"PN1,PN2","1,2","PN3,PN2,PN1,QC1,QC2","3,2,1,4,5"
1111,"PN1,PN3","1,3","PN3,PN2,QC1,PN1,QC2","3,2,4,1,5"
1111,"PN1,QC1","1,4","PN2,PN3,QC1,PN1,QC2","2,3,4,1,5"
1111,"PN1,QC2","1,5","QC1,PN3,PN2,PN1,QC2","4,3,2,1,5"
1111,"PN2,PN3","2,3","PN3,QC1,PN2,PN1,QC2","3,4,2,1,5"
1111,"PN2,QC1","2,4","PN2,QC1,PN3,PN1,QC2","2,4,3,1,5"
1111,"PN2,QC2","2,5","QC1,PN2,PN3,PN1,QC2","4,2,3,1,5"
1111,"PN3,QC1","3,4","QC1,PN1,PN3,PN2,QC2","4,1,3,2,5"
1111,"PN3,QC2","3,5","PN1,QC1,PN3,PN2,QC2","1,4,3,2,5"
1111,"QC1,QC2","4,5","PN3,QC1,PN1,PN2,QC2","3,4,1,2,5"
1111,"PN1,PN2,PN3","1,2,3","QC1,PN3,PN1,PN2,QC2","4,3,1,2,5"
1111,"PN1,PN2,QC1","1,2,4","PN1,PN3,QC1,PN2,QC2","1,3,4,2,5"
1111,"PN1,PN2,QC2","1,2,5","PN3,PN1,QC1,PN2,QC2","3,1,4,2,5"
1111,"PN1,PN3,QC1","1,3,4","PN2,PN1,QC1,PN3,QC2","2,1,4,3,5"
1111,"PN1,PN3,QC2","1,3,5","PN1,PN2,QC1,PN3,QC2","1,2,4,3,5"
1111,"PN1,QC1,QC2","1,4,5","QC1,PN2,PN1,PN3,QC2","4,2,1,3,5"
1111,"PN2,PN3,QC1","2,3,4","PN2,QC1,PN1,PN3,QC2","2,4,1,3,5"
1111,"PN2,PN3,QC2","2,3,5","PN1,QC1,PN2,PN3,QC2","1,4,2,3,5"
1111,"PN2,QC1,QC2","2,4,5","QC1,PN1,PN2,PN3,QC2","4,1,2,3,5"
1111,"PN3,QC1,QC2","3,4,5","QC2,PN1,PN2,PN3,QC1","5,1,2,3,4"
1111,"PN1,PN2,PN3,QC1","1,2,3,4","PN1,QC2,PN2,PN3,QC1","1,5,2,3,4"
1111,"PN1,PN2,PN3,QC2","1,2,3,5","PN2,QC2,PN1,PN3,QC1","2,5,1,3,4"
1111,"PN1,PN2,QC1,QC2","1,2,4,5","QC2,PN2,PN1,PN3,QC1","5,2,1,3,4"
1111,"PN1,PN3,QC1,QC2","1,3,4,5","PN1,PN2,QC2,PN3,QC1","1,2,5,3,4"
1111,"PN2,PN3,QC1,QC2","2,3,4,5","PN2,PN1,QC2,PN3,QC1","2,1,5,3,4"
1111,"PN1,PN2,PN3,QC1,QC2","1,2,3,4,5","PN2,PN1,PN3,QC2,QC1","2,1,3,5,4"
1111,,,"PN1,PN2,PN3,QC2,QC1","1,2,3,5,4"
1111,,,"PN3,PN2,PN1,QC2,QC1","3,2,1,5,4"
1111,,,"PN2,PN3,PN1,QC2,QC1","2,3,1,5,4"
1111,,,"PN1,PN3,PN2,QC2,QC1","1,3,2,5,4"
1111,,,"PN3,PN1,PN2,QC2,QC1","3,1,2,5,4"
1111,,,"PN3,QC2,PN2,PN1,QC1","3,5,2,1,4"
1111,,,"QC2,PN3,PN2,PN1,QC1","5,3,2,1,4"
1111,,,"PN2,PN3,QC2,PN1,QC1","2,3,5,1,4"
1111,,,"PN3,PN2,QC2,PN1,QC1","3,2,5,1,4"
1111,,,"QC2,PN2,PN3,PN1,QC1","5,2,3,1,4"
1111,,,"PN2,QC2,PN3,PN1,QC1","2,5,3,1,4"
1111,,,"PN1,QC2,PN3,PN2,QC1","1,5,3,2,4"
1111,,,"QC2,PN1,PN3,PN2,QC1","5,1,3,2,4"
1111,,,"PN3,PN1,QC2,PN2,QC1","3,1,5,2,4"
1111,,,"PN1,PN3,QC2,PN2,QC1","1,3,5,2,4"
1111,,,"QC2,PN3,PN1,PN2,QC1","5,3,1,2,4"
1111,,,"PN3,QC2,PN1,PN2,QC1","3,5,1,2,4"
1111,,,"QC1,QC2,PN1,PN2,PN3","4,5,1,2,3"
1111,,,"QC2,QC1,PN1,PN2,PN3","5,4,1,2,3"
1111,,,"PN1,QC1,QC2,PN2,PN3","1,4,5,2,3"
1111,,,"QC1,PN1,QC2,PN2,PN3","4,1,5,2,3"
1111,,,"QC2,PN1,QC1,PN2,PN3","5,1,4,2,3"
1111,,,"PN1,QC2,QC1,PN2,PN3","1,5,4,2,3"
1111,,,"PN1,QC2,PN2,QC1,PN3","1,5,2,4,3"
1111,,,"QC2,PN1,PN2,QC1,PN3","5,1,2,4,3"
1111,,,"PN2,PN1,QC2,QC1,PN3","2,1,5,4,3"
1111,,,"PN1,PN2,QC2,QC1,PN3","1,2,5,4,3"
1111,,,"QC2,PN2,PN1,QC1,PN3","5,2,1,4,3"
1111,,,"PN2,QC2,PN1,QC1,PN3","2,5,1,4,3"
1111,,,"PN2,QC1,PN1,QC2,PN3","2,4,1,5,3"
1111,,,"QC1,PN2,PN1,QC2,PN3","4,2,1,5,3"
1111,,,"PN1,PN2,QC1,QC2,PN3","1,2,4,5,3"
1111,,,"PN2,PN1,QC1,QC2,PN3","2,1,4,5,3"
1111,,,"QC1,PN1,PN2,QC2,PN3","4,1,2,5,3"
1111,,,"PN1,QC1,PN2,QC2,PN3","1,4,2,5,3"
1111,,,"QC2,QC1,PN2,PN1,PN3","5,4,2,1,3"
1111,,,"QC1,QC2,PN2,PN1,PN3","4,5,2,1,3"
1111,,,"PN2,QC2,QC1,PN1,PN3","2,5,4,1,3"
1111,,,"QC2,PN2,QC1,PN1,PN3","5,2,4,1,3"
1111,,,"QC1,PN2,QC2,PN1,PN3","4,2,5,1,3"
1111,,,"PN2,QC1,QC2,PN1,PN3","2,4,5,1,3"
1111,,,"PN3,QC1,QC2,PN1,PN2","3,4,5,1,2"
1111,,,"QC1,PN3,QC2,PN1,PN2","4,3,5,1,2"
1111,,,"QC2,PN3,QC1,PN1,PN2","5,3,4,1,2"
1111,,,"PN3,QC2,QC1,PN1,PN2","3,5,4,1,2"
1111,,,"QC1,QC2,PN3,PN1,PN2","4,5,3,1,2"
1111,,,"QC2,QC1,PN3,PN1,PN2","5,4,3,1,2"
1111,,,"QC2,QC1,PN1,PN3,PN2","5,4,1,3,2"
1111,,,"QC1,QC2,PN1,PN3,PN2","4,5,1,3,2"
1111,,,"PN1,QC2,QC1,PN3,PN2","1,5,4,3,2"
1111,,,"QC2,PN1,QC1,PN3,PN2","5,1,4,3,2"
1111,,,"QC1,PN1,QC2,PN3,PN2","4,1,5,3,2"
1111,,,"PN1,QC1,QC2,PN3,PN2","1,4,5,3,2"
1111,,,"PN1,PN3,QC2,QC1,PN2","1,3,5,4,2"
1111,,,"PN3,PN1,QC2,QC1,PN2","3,1,5,4,2"
1111,,,"QC2,PN1,PN3,QC1,PN2","5,1,3,4,2"
1111,,,"PN1,QC2,PN3,QC1,PN2","1,5,3,4,2"
1111,,,"PN3,QC2,PN1,QC1,PN2","3,5,1,4,2"
1111,,,"QC2,PN3,PN1,QC1,PN2","5,3,1,4,2"
1111,,,"QC1,PN3,PN1,QC2,PN2","4,3,1,5,2"
1111,,,"PN3,QC1,PN1,QC2,PN2","3,4,1,5,2"
1111,,,"PN1,QC1,PN3,QC2,PN2","1,4,3,5,2"
1111,,,"QC1,PN1,PN3,QC2,PN2","4,1,3,5,2"
1111,,,"PN3,PN1,QC1,QC2,PN2","3,1,4,5,2"
1111,,,"PN1,PN3,QC1,QC2,PN2","1,3,4,5,2"
1111,,,"PN2,PN3,QC1,QC2,PN1","2,3,4,5,1"
1111,,,"PN3,PN2,QC1,QC2,PN1","3,2,4,5,1"
1111,,,"QC1,PN2,PN3,QC2,PN1","4,2,3,5,1"
1111,,,"PN2,QC1,PN3,QC2,PN1","2,4,3,5,1"
1111,,,"PN3,QC1,PN2,QC2,PN1","3,4,2,5,1"
1111,,,"QC1,PN3,PN2,QC2,PN1","4,3,2,5,1"
1111,,,"QC1,PN3,QC2,PN2,PN1","4,3,5,2,1"
1111,,,"PN3,QC1,QC2,PN2,PN1","3,4,5,2,1"
1111,,,"QC2,QC1,PN3,PN2,PN1","5,4,3,2,1"
1111,,,"QC1,QC2,PN3,PN2,PN1","4,5,3,2,1"
1111,,,"PN3,QC2,QC1,PN2,PN1","3,5,4,2,1"
1111,,,"QC2,PN3,QC1,PN2,PN1","5,3,4,2,1"
1111,,,"QC2,PN2,QC1,PN3,PN1","5,2,4,3,1"
1111,,,"PN2,QC2,QC1,PN3,PN1","2,5,4,3,1"
1111,,,"QC1,QC2,PN2,PN3,PN1","4,5,2,3,1"
1111,,,"QC2,QC1,PN2,PN3,PN1","5,4,2,3,1"
1111,,,"PN2,QC1,QC2,PN3,PN1","2,4,5,3,1"
1111,,,"QC1,PN2,QC2,PN3,PN1","4,2,5,3,1"
1111,,,"PN3,PN2,QC2,QC1,PN1","3,2,5,4,1"
1111,,,"PN2,PN3,QC2,QC1,PN1","2,3,5,4,1"
1111,,,"QC2,PN3,PN2,QC1,PN1","5,3,2,4,1"
1111,,,"PN3,QC2,PN2,QC1,PN1","3,5,2,4,1"
1111,,,"PN2,QC2,PN3,QC1,PN1","2,5,3,4,1"
1111,,,"QC2,PN2,PN3,QC1,PN1","5,2,3,4,1"
2222,PN1,2,"PN1,PN2,PN3,QC1,QC2","2,3,4,5,6"
2222,PN2,3,"PN2,PN1,PN3,QC1,QC2","3,2,4,5,6"
2222,PN3,4,"PN3,PN1,PN2,QC1,QC2","4,2,3,5,6"
2222,QC4,5,"PN1,PN3,PN2,QC1,QC2","2,4,3,5,6"
2222,QC5,6,"PN2,PN3,PN1,QC1,QC2","3,4,2,5,6"

piPy · Posted 04-01-2019 06:50 PM

Hi @ballardw,

68 is the all the products, if this is a problem then I just limit to the top 30 products. By the way having a large output data set is not a problem for me.

I have looked at examples of using array and ALLCOMB function but none fit my problem, I'm also examining the ALLPERM as per your suggestion.

Thank you,

SAS Programming

Product Combinations and Permutations By ID

Re: Product Combinations and Permutations By ID

Re: Product Combinations and Permutations By ID

POI- Optimizing Retail with Product Demand Forecasting

Older SAS 9.4 Platform Releases and Product Versions Moving to Limited...

Permutations combinations

Combinations and Permutations Made Easy in SAS Studio 4.3

Combinations and Permutations

Follow Us

What is...

SAS Programming

Join us for our biggest event of the year!

SAS Training: Just a Click Away

Follow Us

What is...