topic Re: How to create a table with all possible permutations in SAS Programming

How to create a table with all possible permutations

Hadrien — Wed, 20 Apr 2016 14:49:22 GMT

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

Re: How to create a table with all possible permutations

ballardw — Wed, 20 Apr 2016 15:09:20 GMT

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.

Re: How to create a table with all possible permutations

PGStats — Wed, 20 Apr 2016 15:55:41 GMT

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;

Re: How to create a table with all possible permutations

Hadrien — Wed, 20 Apr 2016 16:49:19 GMT

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

Re: How to create a table with all possible permutations

Ksharp — Thu, 21 Apr 2016 02:48:32 GMT

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;

Re: How to create a table with all possible permutations

Hadrien — Thu, 21 Apr 2016 14:37:30 GMT

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.

Re: How to create a table with all possible permutations

Ron_MacroMaven — Thu, 21 Apr 2016 14:51:05 GMT

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

permutation, generating with PLAN procedure

Re: How to create a table with all possible permutations

Ksharp — Fri, 22 Apr 2016 01:02:07 GMT

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