BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.

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,

1 ACCEPTED SOLUTION

Accepted Solutions
CColpaert
SAS Employee

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

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;

View solution in original post

4 REPLIES 4
art297
Opal | Level 21

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
Super User

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
Tourmaline | Level 20

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

Data never sleeps
CColpaert
SAS Employee

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

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;

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 4886 views
  • 6 likes
  • 4 in conversation