topic Re: How to make a Cartesian product, by group, of character observations in SAS Programming

How to make a Cartesian product, by group, of character observations

KS99 — Tue, 06 Apr 2021 19:26:58 GMT

Hi, greetings,

If anyone can help me with this, I will greatly appreciate it.

Suppose I have the following dataset,

data HAVE;
input ID year $ country;
cards;
1 2000 USA
1 2000 ESP
2 2004 GBR
2 2004 USA
2 2004 NLD
2 2005 USA
2 2005 DEU
2 2005 ISR
2 2005 CHE

;
run;

I want to create a new dataset that looks like below,

__________________________

ID year country1 country2;
1 2000 USA ESP

2 2004 GBR USA
2 2004 USA NLD
2 2004 NLD GBR

2 2005 USA DEU
2 2005 USA ISR
2 2005 USA CHE
2 2005 DEU ISR

2 2005 DEU CHE
2 2005 ISR CHE

.....

....

...
________________________

In other words, I want to form a every possible (Cartesian) pair-product of countries, by ID and year, without duplicates (for example, DEU-CHE and CHE-DEU are the same, therefore only one of the two are entered).

Many times I obtain a great help from SAS communities, and

if you can help me one more time this time, I will be more than thankful to you guys.

Have a wonderful day!

KS -,

Re: How to make a Cartesian product, by group, of character observations

Reeza — Tue, 06 Apr 2021 19:31:57 GMT

CROSS JOIN with year and ensure that one side is greater than the other side to avoid duplicates.

Re: How to make a Cartesian product, by group, of character observations

KS99 — Tue, 06 Apr 2021 20:31:28 GMT

But they are character variables. How can I determine one side is quantitatively greater than the other side?

Re: How to make a Cartesian product, by group, of character observations

Reeza — Tue, 06 Apr 2021 20:42:27 GMT

Try the suggestion. Alphabetical order for characters will work as expected.

Re: How to make a Cartesian product, by group, of character observations

KS99 — Tue, 06 Apr 2021 20:45:43 GMT

Oh really? Thank you !!

Re: How to make a Cartesian product, by group, of character observations

KS99 — Tue, 06 Apr 2021 20:59:15 GMT

I am such a Newbie in SAS..

Can you provide a clue code to achieve this?

Many thanks!

KS -,

Re: How to make a Cartesian product, by group, of character observations

Sajid01 — Tue, 06 Apr 2021 23:32:02 GMT

From your requirement you want combination of countries by year and ID and no duplicates.
From the required output given in your question you want all combination of countries.
While there are many approaches to the solution, with the understanding I have of your requirements, I suggest the following code.
This is an example try modifications / improvements as needed.

data HAVE; 
input  ID  year  country $;
cards;
1  2000 USA
1  2000 ESP
2  2004 GBR
2  2004 USA
2  2004 NLD
2  2005 USA
2  2005 DEU
2  2005 ISR
2  2005 CHE

;
run; 
proc sql;
create table want as
select a.* , b.country as country2 from have  a cross join have  b
where a.country ne b.country
order by id, year, country;
run;

Re: How to make a Cartesian product, by group, of character observations

ChrisNZ — Wed, 07 Apr 2021 00:55:45 GMT

where a.country gt b.country

Re: How to make a Cartesian product, by group, of character observations

KS99 — Thu, 08 Apr 2021 00:38:43 GMT

Thank you ChrisNZ !

Your code was producing Cartesian productions of all countries against all countries;

But it gave me a clue how to do it.

I separated two datasets, and using a chain of where (a.ID=b.ID and a.year=b.year and a.country gt b.country1),

obtained my wished-for results.