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
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;
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.
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;
Thanks for the correction, the order does not matter therefore it is a combination. This solution worked great.
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;
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.