topic Re: How to create table of every combination of 0s and 1s? in SAS Programming

How to create table of every combination of 0s and 1s?

EricSASUser — Wed, 11 Dec 2019 19:36:27 GMT

I'm trying to create the table below to hold different flag combinations using zeros and ones. If I already know my column names (Apples, Oranges, Bananas, Kiwis) is there a way to easily generate the table below without manually each individual value?

Example

Apples	Oranges	Bananas	Kiwis
0	0	0	0
0	0	1	0
0	0	0	1
0	0	1	1
0	1	0	0
0	1	1	0
0	1	0	1
0	1	1	1
1	0	0	0
1	0	1	0
1	0	0	1
1	0	1	1
1	1	0	0
1	1	1	0
1	1	0	1
1	1	1	1

Re: How to create table of every combination of 0s and 1s?

Reeza — Wed, 11 Dec 2019 20:00:04 GMT

https://blogs.sas.com/content/iml/2016/02/24/create-a-design-matrix-in-sas.html

Re: How to create table of every combination of 0s and 1s?

FreelanceReinh — Wed, 11 Dec 2019 20:44:15 GMT

Hello @EricSASUser and welcome to the SAS Support Communities!

One of several more elementary ways to create this table is to use nested DO loops:

data want;
retain Apples Oranges Bananas Kiwis; /* variable order */
do Apples =0,1;
  do Oranges=0,1;
    do Kiwis  =0,1;
      do Bananas=0,1;
        output;
      end;
    end;
  end;
end;
run;

Note that the RETAIN statement could be omitted if the column order matched the "nesting order."

Here's another way, using the binary system:

data want(drop=i j);
retain Apples Oranges Bananas Kiwis; /* variable order */
array a[*] Apples Oranges Kiwis Bananas;
do i=0 to 2**dim(a)-1;
  do j=1 to dim(a);
    a[j]=~~band(i,2**(dim(a)-j));
  end;
  output;
end;
run;

Re: How to create table of every combination of 0s and 1s?

PaigeMiller — Wed, 11 Dec 2019 20:10:22 GMT

You can use PROC FACTEX to create a full factorial design.

https://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.4&docsetId=qcug&docsetTarget=qcug_factex_gettingstarted02.htm&locale=en

Which is easier (in my opinion) and less prone to errors than creating the necessary do loops to obtain the desired result. And I don't think the link from @Reeza really addresses how to create a table of all combinations of 0s and 1s.

Re: How to create table of every combination of 0s and 1s?

Ksharp — Thu, 12 Dec 2019 12:54:54 GMT

data have;
array x{*} Apples Oranges Bananas Kiwis;
k=-1;
n=2**dim(x);
do i=1 to n;
 rc=graycode(k,of x{*});
 output;
end;
drop i k n rc;
run;

Re: How to create table of every combination of 0s and 1s?

FreelanceReinh — Thu, 12 Dec 2019 12:42:59 GMT

Hi @Ksharp: You were lucky that 4**2 = 2**4. 🙂 But in general the number of combinations should be

n=2**dim(x);

Re: How to create table of every combination of 0s and 1s?

Ksharp — Thu, 12 Dec 2019 12:56:00 GMT

Opps. There was some thing bad in my mind. Thanks for correct that .

Re: How to create table of every combination of 0s and 1s?

gamotte — Thu, 12 Dec 2019 13:28:34 GMT

Hello,

data bin;
    input b;
    cards;
0
1
;
run;

proc sql noprint;
    CREATE TABLE fruits AS
    SELECT a.b AS Apples, b.b AS Oranges, c.b AS Bananas, d.b AS Kiwis
    FROM bin a, bin b, bin c, bin d
    ;
quit;

Re: How to create table of every combination of 0s and 1s?

PaigeMiller — Thu, 12 Dec 2019 13:40:56 GMT

Hi, @gamotte this is very clever and it works and I wish I had thought of it.

But I am wondering what the reason is that the values in the columns are not in the order I would expect. Since the code has apples first, oranges second, bananas third and kiwis fourth, I would expect apples would show 8 zeros followed by 8 ones (it does), then I expect the second column oranges to show four zeros followed by four ones, repeated twice (it does), then I expect the third column bananas as two zeros followed by two ones, repeated four times (it does not), and then the fourth column kiwis as a zero followed by a 1 repeated 8 times (it does not). Can anyone explain why this unexpected (to me) ordering is happening from this SQL code?

Re: How to create table of every combination of 0s and 1s?

Oligolas — Thu, 12 Dec 2019 13:53:10 GMT

#jff 😉

%MACRO cp(dsout=,fruits=,n=);
   %local c currItem;
   %let c=1;
   %let currItem=%scan(&fruits.,&c.,%str( ));
   %DO %WHILE(%length(&currItem.)>0);
      DATA &currItem.;
         %do &currItem.=0 %to &n.;
            &currItem.=&&&currItem.;output;
         %end;
      RUN;
      %let c=%eval(&c.+1);
      %let currItem=%scan(&fruits.,&c.,%str( ));
   %END;

   DATA _NULL_;
      call execute("PROC SQL;CREATE TABLE cartesian AS SELECT * FROM %sysfunc(translate(&fruits.,',',' '));");
      call execute("drop table %sysfunc(translate(&fruits.,',',' '));QUIT;");
   RUN;
   PROC PRINT;RUN;
%MEND cp;
%cp(dsout=cartesian,fruits=apples oranges bananas kiwis,n=1);

Re: How to create table of every combination of 0s and 1s?

gamotte — Thu, 12 Dec 2019 14:03:08 GMT

Hi, @PaigeMiller and thanks for your kind comments.

I don't know why the columns are ordered that (unintuitive) way.

It seems that the possible values are first looped on the third column, then the fourth, fifth and so on...

second and first columns are the last ones where the loop on values occurs.

Here is an (incomplete) example with more values and columns

I guess it may just be a consequence of the particular algorithm used to perform cartesian

product.

Re: How to create table of every combination of 0s and 1s?

Oligolas — Thu, 12 Dec 2019 14:57:42 GMT

data cartesian(drop=i j);
   array fruits apples bananas oranges kiwis;
   do i=1 to 2**dim(fruits);
      do j=1 to dim(fruits);
         fruits(j)=input(substr(put(i,binary.),j,1),best.);
      end;
      output;
   end;
run;

Re: How to create table of every combination of 0s and 1s?

FreelanceReinh — Thu, 12 Dec 2019 16:57:50 GMT

@PaigeMiller: Good question. I also don't know the algorithm used by PROC SQL behind the scenes which determines the sort order. I would add an ORDER BY clause to be on the safe side (independent of platform, OS, SAS version, ... influences):

order by 1,2,4,3;

The _METHOD option of the PROC SQL statement reveals that there is some sort of tree-type hierarchy (keeping pairs of adjacent variables together) in the way the query is structured internally. So it's definitely not just "linear." Here's the log for seven (instead of four) variables:

NOTE: SQL execution methods chosen are:

      sqxcrta
          sqxjsl
              sqxjsl
                  sqxsrc( WORK.BIN(alias = G) )
                  sqxjsl
                      sqxsrc( WORK.BIN(alias = D) )
                      sqxsrc( WORK.BIN(alias = C) )
              sqxjsl
                  sqxjsl
                      sqxsrc( WORK.BIN(alias = A) )
                      sqxsrc( WORK.BIN(alias = B) )
                  sqxjsl
                      sqxsrc( WORK.BIN(alias = E) )
                      sqxsrc( WORK.BIN(alias = F) )

On my computer the sort orders for 2, 3, ..., 8 variables are:

# vars  order
   2    1,2
   3    3,1,2
   4    1,2,4,3
   5    1,2,5,4,3
   6    5,6,1,2,4,3
   7    7,4,3,1,2,5,6
   8    1,2,4,3,7,8,5,6

Edit: Luckily, other procedures that could be used produce predictable results, e.g., PROC SUMMARY:

data fruits;
output;
Apples+1; Oranges+1; Kiwis+1; Bananas+1;
output;
run;

proc summary data=fruits nway completetypes;
class _all_;
output out=want(drop=_:); /* different variable order, though */
run;

Or PROC FREQ:

proc freq data=fruits noprint;
tables Apples*Oranges*Kiwis*Bananas / sparse out=want(label=' ' drop=count percent);
run;                                 /* different variable order, though */

Re: How to create table of every combination of 0s and 1s?

EricSASUser — Fri, 13 Dec 2019 19:17:52 GMT

Thanks everyone. This is great.

Re: How to create table of every combination of 0s and 1s?

EricSASUser — Fri, 13 Dec 2019 19:49:59 GMT

This might be easiest way.