BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Hadrien
Obsidian | Level 7

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
1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

7 REPLIES 7
ballardw
Super User

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.

 

PGStats
Opal | Level 21

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
Hadrien
Obsidian | Level 7

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

Ksharp
Super User

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;

 

 

 

 

Hadrien
Obsidian | Level 7

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.
Ksharp
Super User
Yeah. Maybe your site don't have IML product. But you can run it under SAS University Edition. It is totally free.
Ron_MacroMaven
Lapis Lazuli | Level 10
permutations can be done with proc plan
check your OnLine doc for this topic:

permutation, generating with PLAN procedure

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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