cartesian product (cross join)

[CColpaert]

Hello,

I'm looking to create a cartesian product with 3+ tables.

TableA:
------
Columns for TableA: colA
Values for colA: product A, product B, product C (three rows)

TableB:
------
Columns for TableB: colB
Values for colB: free, cheep, normal, expensive (four rows)

TableC:
------
Columns for TableC: colC
Values for colC: low, medium, high;

Query:
-----
select tableA.colA, tableB.colB, tableC.colC from tableA,tableB,tableC;

Result:
------

COLA COLB COLC
product A free low
product A free medium
product A free high
product B free low
product B free medium
product B free high
product C free low
product C free medium
product C free high
product A cheep low
product A cheep medium
product A cheep high
product B cheep low
product B cheep medium
product B cheep high
product C cheep low
product C cheep medium
product C cheep high
product A normal low
product A normal medium
product A normal high
product B normal low
product B normal medium
product B normal high
product C normal low
product C normal medium
product C normal high
product A expensive low
product A expensive medium
product A expensive high
product B expensive low
product B expensive medium
product B expensive high
product C expensive low
product C expensive medium
product C expensive high

Instead, I want this result:
----------------------------

COLA COLB COLC
product A free low
product A free medium
product A free high
product A cheep low
product A cheep medium
product A cheep high
product A normal low
product A normal medium
product A normal high
product A expensive low
product A expensive medium
product A expensive high
product B free low
product B free medium
product B free high
product B cheep low
product B cheep medium
product B cheep high
product B normal low
product B normal medium
product B normal high
product B expensive low
product B expensive medium
product B expensive high
product C free low
product C free medium
product C free high
product C cheep low
product C cheep medium
product C cheep high
product C normal low
product C normal medium
product C normal high
product C expensive low
product C expensive medium
product C expensive high

So the order is like this:

- first value colA
-- first value colB
--- first value colC
--- second value colC
--- third value colC
-- second value colB
--- first value colC
--- second value colC
--- third value colC
-- third value colB
--- first value colC
--- second value colC
--- third value colC
- second value colB
....

Any idea on how to do this?

Full code is attached. Uncomment "%*let COVd_colD = low, medium, high;" to test with 4 columns/variables.

Best regards,

[art297]

You could probably convert this to be all sql, but I have some other things I have to do this afternoon and this was the quickest working solution I could come up with.  It uses a mixture of proc format, a data step and, finally, proc sql:

%let COVa_colA = product A, product B, product C;

%let COVb_colB = free, cheep, normal, expensive;

%let COVc_colC = low, medium, high;

proc format;

  invalue $orderb

    'free'=1

    'cheap'=2

    'normal'=3

    'expensive'=4;

  invalue $orderc

    'low'=1

    'medium'=2

    'high'=3;

run;

data temp1 (keep=cola)

     temp2 (keep=cola)

     temp3 (keep=cola);

  set sashelp.vmacro (where=(

  scope eq 'GLOBAL' and prxmatch('/^COV\w_.+$/i',name )));

  i=1;

  do while (scan(value,i) ne "");

    cola=scan(value,i);

    if name =: "COVA" then do;

      cola=catx(" ",cola,scan(value,i+1));

      output temp1;

      i+2;

    end;

    else if name =: "COVB" then do;

      output temp2;

      i+1;

    end;

    else if name =: "COVC" then do;

      output temp3;

      i+1;

    end;

  end;

run;

proc sql noprint;

  create table want as

  select *

    from temp1,

         temp2 (rename=(cola=colb)),

         temp3 (rename=(cola=colc))

      order by cola,input(colb,$orderb.),

          input(colc,$orderc.);

  ;

quit;

[Ksharp]

How about:

It is a big advantage of SAS . keeping the original order of obs.

But it is not as efficient as SQL.

data TableA ;
input colA $10.;;
cards;
product A
product B
product C
;
run;
data TableB ;
input colB $10.;;
cards;
free
cheep
normal
expensive
;
run;
data TableC ;
input colC $10.;;
cards;
low
medium
high
;
run;
data want;
 set tableA;
 do i=1 to _nobs;
  set tableB nobs=_nobs point=i;
   do j=1 to __nobs;
    set tableC nobs=__nobs point=j;
    output;
   end;
 end;
run;

Ksharp

[LinusH]

Without having dug into your attachment - I think this can be solved if you can have some rank/sort order variables in your source tables:

select tableA.colA, tableB.colB, tableC.colC

     from tableA,tableB,tableC

     order by tablA.OrderColA, tableB.OrderColB, tableC.OrderColC

;

/Linus

[CColpaert · Accepted solution]

Thanks for your help guys. Great example of how you can achieve a result in various ways

The following code also works as expected:

%let COVa_product = product A, product B, product C;

%let COVb_price = free, cheep, normal, expensive;

%let COVc_quality = low, medium, high;

%macro create_prod;

    proc sql noprint;

        select name, count(*) into :covs separated by ',', :n_covs

            from sashelp.vmacro

            where scope = 'GLOBAL' and prxmatch('/^COV\w_.+$/i',name )

            order by name ;

    quit;

    %put count: &n_covs;

    %put covs: &covs;

    data all (drop=_:);

    %do i = 1 %to &n_covs;

        %let cov = %scan(%quote(&covs),&i,',');

        %let var = %scan(&cov,2,_);

            length _%scan(&cov,2,_) $ 200 %scan(&cov,2,_) $ 30;

            _%scan(&cov,2,_) = symget("&cov");

            do _i_%scan(&cov,2,_) = 1 to countw(_%scan(&cov,2,_),',');

                %scan(&cov,2,_) = strip(scan(_%scan(&cov,2,_),_i_%scan(&cov,2,_),','));

                *putlog "%scan(&cov,2,_):" %scan(&cov,2,_);

    %end;

            output;

    %do i = 1 %to &n_covs;

            end;

    %end;

     run;

%mend;

%create_prod;