topic cartesian product (cross join) in SAS Procedures

cartesian product (cross join)

CColpaert — Mon, 05 Dec 2011 18:17:07 GMT

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,

cartesian product (cross join)

art297 — Mon, 05 Dec 2011 20:38:00 GMT

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;

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;

cartesian product (cross join)

Ksharp — Tue, 06 Dec 2011 03:53:43 GMT

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

cartesian product (cross join)

LinusH — Tue, 06 Dec 2011 09:10:41 GMT

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

Re: cartesian product (cross join)

CColpaert — Tue, 06 Dec 2011 09:14:09 GMT

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;