DATA Step, Macro, Functions and more

How to create a table with all possible permutations

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 15
Accepted Solution

How to create a table with all possible permutations

Hi, I am new to SAS and can't figure out how to create a table with all possible permutations from multiple vectors (I actually don't know how to create the vectors either). Could someone point me in the right direction on how to do this?

 

Here is an example of what I am trying to do and how it would be done in R using the expand.grid() function:

 

#Create vectors
states <- c("MA", "NY")
seasons <- c("Fall", "Winter", "Spring", "Summer")
activities <- c("TV", "Board Games")

#Create data frame with all permutations
permutations <- expand.grid(states, seasons, activities)

The resulting "permutations" table would be:

   Var1   Var2        Var3
1    MA   Fall          TV
2    NY   Fall          TV
3    MA Winter          TV
4    NY Winter          TV
5    MA Spring          TV
6    NY Spring          TV
7    MA Summer          TV
8    NY Summer          TV
9    MA   Fall Board Games
10   NY   Fall Board Games
11   MA Winter Board Games
12   NY Winter Board Games
13   MA Spring Board Games
14   NY Spring Board Games
15   MA Summer Board Games
16   NY Summer Board Games

Accepted Solutions
Solution
‎04-20-2016 12:47 PM
Respected Advisor
Posts: 4,920

Re: How to create a table with all possible permutations

Those are combinations. One easy way to get all combinations is the cartesian product operation in SQL:

 

data states;
do state = "MA", "NY"; output; end; run;
data seasons;
length season $8;
do season = "Fall", "Winter", "Spring", "Summer"; output; end; run;
data activities;
length activity $12;
do activity = "TV", "Board Games"; output; end; run;

proc sql;
select * from states, seasons, activities;
quit;
PG

View solution in original post


All Replies
Super User
Posts: 11,343

Re: How to create a table with all possible permutations

One way

data want;
   array s {2} $ 2   _temporary_ ('MA','NY');
   array t {4} $ 8   _temporary_ ('Spring','Summer','Fall','Winter');
   array a {2} $ 15  _temporary_ ('TV','Board Games');
   Do i=1 to dim(s);
      State=s[i];
      do j= 1 to dim(t);
         Season = t[j];
         do k = 1 to dim(a);
            Activity= a[k];
            output;
         end;
      end;
   end;
   keep state season activity;
run;

The critical parts of the arrays: the number of elements in the braces should match the number of items in parentheses, the length - number after the $- should be as large as the length of the longest element entered.

 

Solution
‎04-20-2016 12:47 PM
Respected Advisor
Posts: 4,920

Re: How to create a table with all possible permutations

Those are combinations. One easy way to get all combinations is the cartesian product operation in SQL:

 

data states;
do state = "MA", "NY"; output; end; run;
data seasons;
length season $8;
do season = "Fall", "Winter", "Spring", "Summer"; output; end; run;
data activities;
length activity $12;
do activity = "TV", "Board Games"; output; end; run;

proc sql;
select * from states, seasons, activities;
quit;
PG
Occasional Contributor
Posts: 15

Re: How to create a table with all possible permutations

Thanks for the correction, the order does not matter therefore it is a combination. This solution worked great.

Super User
Posts: 10,023

Re: How to create a table with all possible permutations

There is a similiar function in IML .

 

 

proc iml;
states={"MA", "NY"};
seasons={"Fall", "Winter", "Spring", "Summer"};
activities={"TV", "Board Games"};
want=expandgrid(states,seasons,activities);

print want;
quit;

 

 

 

 

Occasional Contributor
Posts: 15

Re: How to create a table with all possible permutations

[ Edited ]

This is a very elegant solution, thank you! Unfortunately I got the following error message, perhaps it has to do with my version of SAS:

 

ERROR: The SAS/IML product with which IML (2) is associated is either not licensed for your system or the product license has 
       expired. Please contact your SAS installation representative.
ERROR: Bad product ID for procedure IML.
Super User
Posts: 10,023

Re: How to create a table with all possible permutations

Yeah. Maybe your site don't have IML product. But you can run it under SAS University Edition. It is totally free.
Regular Contributor
Posts: 227

Re: How to create a table with all possible permutations

permutations can be done with proc plan
check your OnLine doc for this topic:

permutation, generating with PLAN procedure
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 355 views
  • 6 likes
  • 5 in conversation