Create a table with all permutations of some column values in SAS

Question

I am working in SAS Enterprise guide and want to create a table that contains all possible permutations of some columns. Here is an example:

Lets say I have three columns

apple                   pear                      plum  

0                       good                      blue
1                       middle                    violet
                        bad

I would want my output table to look as follows:

apple        pear       plum

0            good       blue
0            good       violet
0            middle     blue
0            middle     violet
0            bad        blue
0            bad        violet
1            good       blue
1            good       violet
1            middle     blue
1            middle     violet
1            bad        blue 
1            bad        violet

My actual code has more columns with more distinct values, so hard coding is definitely not an option. How can I create such a table in SAS?

Thanks up front for the help!

What is it you don't want to hardcode? The values or the variable names? It is easy to avoid hardcoding the values. Harder to avoid using the variable names. — Tom, Mar 20 '17 at 16:43
I don't want to hardcode each observation. Variable names are fine. — Martin Reindl, Mar 20 '17 at 16:47

Tom · Accepted Answer · 2017-03-20T17:15:09.983

1

You can use PROC SQL to create full cross product.

proc sql ;
  create table want as
    select * 
    from (select distinct apple from have where not missing(apple))
       , (select distinct pear from have where not missing(pear))
       , (select distinct plum from have where not missing(plum))
  ;
quit;

edited Mar 20 '17 at 17:15

answered Mar 20 '17 at 17:08

Tom

47,574
2
16
29

score 1 · Answer 2 · answered Mar 20 '17 at 17:27

PROC SUMMARY

data testx;
   input apple pear $ plum $;
   cards;
0 good   blue
1 middle violet
1 bad    blue
;;;;
   run;
proc summary nway completetypes chartype;
   class _all_;
   output out=testb(drop=_:);
   run;
proc print;
   run;

Obs    apple    pear       plum

  1      0      bad       blue  
  2      0      bad       violet
  3      0      good      blue  
  4      0      good      violet
  5      0      middle    blue  
  6      0      middle    violet
  7      1      bad       blue  
  8      1      bad       violet
  9      1      good      blue  
 10      1      good      violet
 11      1      middle    blue  
 12      1      middle    violet

Note that if your input data had missing values for APPLE and PLUM on the last observation then PROC SUMMARY will eliminate the PEAR='bad' records and only generate 8 instead of 12 records. If you add MISSING option then PEAR='bad' is kept, but also the missing values of APPLE and PLUM are output. — Tom, Mar 20 '17 at 17:35

Create a table with all permutations of some column values in SAS

2 Answers2