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 -,
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;
But they are character variables. How can I determine one side is quantitatively greater than the other side?
Try the suggestion. Alphabetical order for characters will work as expected.
Oh really? Thank you !!
I am such a Newbie in SAS..
Can you provide a clue code to achieve this?
Many thanks!
KS -,
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;
where a.country gt b.country
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.
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 how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.