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,
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;
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;
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
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
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.