topic How to get Cartesian product in Datastep in SAS Programming

How to get Cartesian product in Datastep

kuridisanjeev — Thu, 25 Apr 2013 12:11:53 GMT

Hello All.

Is there any way to get Cartesian product between two datasets in Datasetp???

I was doing some learning process on difference between Merge and Joins.

I can able to get left join and right joins in the datastep by using IN options.but i am not sure how to get Cartesian product in the datastep.

It it not my requirement but i just want know how to achieve this.

Could any one help me on this to get clarify..

Thanks& Regards.

Sanjeev.K

Re: How to get Cartesian product in Datastep

RichardinOz — Thu, 25 Apr 2013 12:23:24 GMT

Sanjeev

You may get someone providing you with a working solution to obtaining a Cartesian product using a data step but why would you bother? Proc SQL does this without any fuss. Anything else is just creating a roundabout way to solve a simple problem. Do you have a business case for what you are asking?

Richard

Re: How to get Cartesian product in Datastep

esjackso — Thu, 25 Apr 2013 12:26:27 GMT

It isnt part of the merge on a data step but here is a reference and code for catresian in a datastep

24652 - Generate every combination of observations between data sets

data every_combination;

/* Set one of your data sets, usually the larger data set */

set one;

do i=1 to n;

/* For every observation in the first data set, */

/* read in each observation in the second data set */

set two point=i nobs=n;

output;

end;

run;

Hope that helps!

UPDATE -- Richard is right -- learning aside -- I did cartesians in SQL only

Re: How to get Cartesian product in Datastep

Ksharp — Thu, 25 Apr 2013 12:39:41 GMT

And of course .don't forget Hash Table could also get it.

Re: How to get Cartesian product in Datastep

kuridisanjeev — Thu, 25 Apr 2013 13:04:28 GMT

Hi Richardi.

As i already mentioned in my post,"it is not my Actual requirement",but i just want to know. how can we approach the datastep to do this.

In a learning process,i tried all the SQL joins in datastep like Left,Right,Inner,Outer Etc.But i was struck with Cartesian product.I too always prefer SQL join to get Cartesian product because its straight forward and simple and it wont required much coding as well.

I am using This Communities forum as a learning opportunity to get clarify many Hidden this in SAS ,even Those requirements not comes into the picture of real time.

Don't mind if i am posting few silly requirements.

Regards.

Sanjeev.K

Re: How to get Cartesian product in Datastep

Haikuo — Thu, 25 Apr 2013 13:16:18 GMT

FWIW, Eric's code is good when you do Cartesian Product over two tables from top to toe. However, if doing it using BY variables (Doing Cartesian Products within groups, such as many to many join), then Hash() seems to be the only data-step way to go.

On another note, I would generally agree with Richard's comments regarding SQL, which is built natively to do Cartesian product. But from some of my own experiences, sometimes Hash() does hold a performance edge over Proc SQL.

Just my 2 cents,

Haikuo

Re: How to get Cartesian product in Datastep

donevans — Thu, 25 Apr 2013 14:35:33 GMT

If you want all the observations from A and all the observations from B, just "stack the datasets".

data fileAB;

set fileA fileB;

run;

***********************

If you want all the data from A and B with a common identifier (ID).

data fileAB;

merge fileA(in=a) fileB(in=b);

by ID;

if a and b;

run;

Re: How to get Cartesian product in Datastep

Astounding — Thu, 25 Apr 2013 15:18:52 GMT

If you need the Cartesian product within BY variables, there is another way that a DATA step can accomplish this. It's not necessarily the best way, but here's the approach FWIW.

For the smaller data set, sort it. Then construct a format that translates the BY variable's value into two pieces of information: the first and last observation number in the sorted data that matches that BY value.

Then perform the "join". Read in an observation from the larger data set, use the format to retrieve which observation numbers match the value of the "BY" variable, and retrieve them using point=. Altogether, it's probably 20 lines of code.

Re: How to get Cartesian product in Datastep

Haikuo — Fri, 26 Apr 2013 02:46:11 GMT

Wow, Astounding! You are squeezing the last drop of juice from conventional data step, very impressed!

Haikuo

Re: How to get Cartesian product in Datastep

RichardinOz — Sat, 27 Apr 2013 02:01:45 GMT

Well, as I predicted a number of people responded to your challenge, proving

Some people will enjoy devising ingenious solutions to problems even if they have no practical implementation. "Art for Art's sake." Well enough.
The SAS data step is a very powerful beast in the right hands.

I would not regard this exercise as a "learning opportunity to get clarity". Your best learning opportunities are taking the SAS training; reading the SAS documentation (I would recommend bookmarking functions, formats, and commonly used procs like SQL, MEANS, FREQ), and asking your more experienced colleagues to explain something you do not understand.

Richard

Re: How to get Cartesian product in Datastep

AjayKant — Fri, 17 Jan 2014 15:35:16 GMT

Need to understand why SAS system generate merging in data step as SQL is already done all those work .. its all about the way we think ...I think we need more enhance processing we need to do new function and then they created this algorithms .. because any other system can't do what merge is doing here in terms of technical processing .

if they only need to build a Cartesian product they never find the this merge process ..

and if there is possible to generate the Cartesian product .. i am also interested to know .

But my think is always towards find the efficient and more simple function of processing.

Re: How to get Cartesian product in Datastep

jakarman — Sun, 19 Jan 2014 08:18:05 GMT

As you are in learning approach.

The cartesian product is mathematical simple but can be for real execution a no go having bigger datasets.

Data Points: Five Ways to Rev up Your SQL Performance

For real big data problems there is move to NOSQL NoSQL - Wikipedia, the free encyclopedia

Do we not have something there that is nosql?.... SAS datastep

Re: How to get Cartesian product in Datastep

Tom — Sun, 19 Jan 2014 15:18:03 GMT

The SAS data step pre-dates the commercial implementation of SQL.

Re: How to get Cartesian product in Datastep

jakarman — Sun, 19 Jan 2014 15:28:08 GMT

Hashing in SAS was added with SAS V9. SQL was already present in SAS V6.

NoSql is hyping today but with a closer look at that the same kind of technical approaches as in the time of the PC-XT.

The basic question is also the same:

"what to do when a simple logical approach is going beyond comfort-zone when trying to implement it".

Re: How to get Cartesian product in Datastep

Brian_C_Brown — Fri, 11 Dec 2015 19:20:59 GMT

I think a hash table would look up at most one row. So the resulting dataset would not be a Cartesian product.

Re: How to get Cartesian product in Datastep

Brian_C_Brown — Fri, 11 Dec 2015 19:23:23 GMT

I think you could get a Cartesian product if you perform a MERGE using variables with one single common value for all rows in both datasets.

Re: How to get Cartesian product in Datastep

Ricardo_Neves — Thu, 12 Oct 2017 09:37:22 GMT

Hi,

I have a process where I do a Cartesian product of 2 tables (one with data and one with parameters) using proc sql and then I use data step to apply the needed filters so that at the end only the lines where the data matches the parameter remain.

As you can imagine this is a very inefficient process and so I'm trying to use this approach to and/or merge to apply the filters during the cross join so that the process becomes more effecient. But unfortunately it doesn't seem to be working since and don't get the same results in both cases.

Here is a sample of what I'm doing:

    data campanha_000_testes;
    set base
      do i=1 to n;

        set parameter point=i nobs=n;


    if id = 000 and SYSTEM NE 'BBBB' then delete;

        
            if not missing('oferta Origem'n) then
                do;
                    if compress('oferta Origem'n) = compress(pack_dsc);
                end;

                    if find(RP_ID_EXCLUDE,compress(put(pack_cod,10.))) then delete;
                end;

            if not missing(Desconto_mens_max) then
                do;
                    if Desconto_mens_min <= Beneficio <=Desconto_mens_max;
                end;

            if not missing(mensalidade_maxima) then
                do;
                    *distinguir a abordagem;
                    if abordagem in ('Client','Conta') and id ne 303 then
                        do;
                            if mensalidade_minima < mens_liq_conta <= mensalidade_maxima;
                        end;
                    else if compress(abordagem) in ('Client','Conta') and id = 303 then
                        do;
                            if mensalidade_minima < mens_liq_im_conta <= mensalidade_maxima;
                        end;
                    else if abordagem = 'NIF' then
                        do;
                            if mensalidade_minima < mens_liq_NIF <= mensalidade_maxima;
                        end;
                    else
                        do;
                            if Mensalidade_Minima < Mensalidade_Liq_Siva <= Mensalidade_maxima;
                        end;
                end;

            output;

      end;

        run;

Any ideas on how to perform this?

Thanks in advance

Re: How to get Cartesian product in Datastep

Patrick — Thu, 12 Oct 2017 11:32:01 GMT

@Brian_C_Brown

You can iterate over the full hash table either by using an ITER object or by loading the hash with a single value key for all rows and then use the do_over() method.

Re: How to get Cartesian product in Datastep

Patrick — Thu, 12 Oct 2017 11:35:36 GMT

@Brian_C_Brown wrote:

I think you could get a Cartesian product if you perform a MERGE using variables with one single common value for all rows in both datasets.

@Brian_C_Brown

That won't work! And the reason why it won't work will explain the main difference between the outcome of a SQL join and a SAS data step merge. I suggest you give it a go with some sample data and code and then try to understand what happened - or come back here and ask the question.

Re: How to get Cartesian product in Datastep

UmaSingh — Fri, 10 Feb 2023 05:46:27 GMT

THanks. The code really helped me to get cartesian type prod