Distinc pairs of cities using proc sql

Solved
Super Contributor
Posts: 464

Distinc pairs of cities using proc sql

any idea,anyone pleasE?

data places;
input pid city \$12.;
cards;
1 LosAngeles
2 Orlando
3 London
4 NewYork
5 Boston
6 Paris
7 Washington
8 LosAngeles
9 Orlando
10 London
;
run;

Accepted Solutions
Solution
‎04-22-2013 03:56 PM
Frequent Contributor
Posts: 129

Re: Distinc pairs of cities using proc sql

Then adding distinct  and order by clause to PGStats second guess will get you this result.

proc sql;

create table pairs2 as

select distinct A.city as city1, B.city as city2

from places as A inner join places as B

on A.city < B.city

order by a.city, b.city

select * from pairs2;

quit;

All Replies
Frequent Contributor
Posts: 129

Re: Distinc pairs of cities using proc sql

Tal,

Your requirements are very sketchy here, so I am making some assumptions.  What about:

proc sql ;

create table paired_cities as

select distinct city

,min(pid) as small_pid

,max(pid) as large_pid

from places

group by city

having count(*) = 2
;

quit ;

Super Contributor
Posts: 578

Re: Distinc pairs of cities using proc sql

you can easily generate pairs this way:

proc sql;

create table pairs as

select

t1.pid as Pid_1,

t1.city as City_1,

t2.pid as PId_2,

t2.city as City_2

from

places p1, places p2

where

p1.pid ne p2.pid;

quit;

One issue might be that this approach includes both LosAngeles, Orlando and Orlando, LosAngeles.  You can avoid this by:

proc sql;

create table unique_paris as

select

t1.pid as PId_1,

t1.city as City_1,

t2.pid as PId_2,

t2.city as City_2

from

places t1, places t2

where

t2.pid > t1.pid;

quit;

Esteemed Advisor
Posts: 5,521

Re: Distinc pairs of cities using proc sql

What do you want? Pairs of cities, pairs of PIDs, pairs of city-PID combinations.

Let me join the guessing game! Here are two more guesses as to what you may want:

proc sql;

create table pairs1 as

select city, min(pid) as pid1, max(pid) as pid2 from places

group by city having range(pid) > 0;

select * from pairs1;

create table pairs2 as

select A.city as city1, B.city as city2

from places as A inner join places as B

on A.city < B.city;

select * from pairs2;

quit;

Excuse me if I don't spend too much time on those answers, you obviously didn't spend much on the question...

PG

PG
Super Contributor
Posts: 464

Re: Distinc pairs of cities using proc sql

sorry guys i was not more specific

this is what i am supposed to get:

1 Boston London
2 Boston LosAngeles
3 Boston NewYork
4 Boston Orlando
5 Boston Paris
6 Boston Washington
7 London LosAngeles
8 London NewYork
9 London Orlando
10 London Paris
11 London Washington
12 LosAngeles NewYork
13 LosAngeles Orlando
14 LosAngeles Paris
15 LosAngeles Washington
16 NewYork Orlando
17 NewYork Paris
18 NewYork Washington
19 Orlando Paris
20 Orlando Washington
21 Paris Washington

Solution
‎04-22-2013 03:56 PM
Frequent Contributor
Posts: 129

Re: Distinc pairs of cities using proc sql

Then adding distinct  and order by clause to PGStats second guess will get you this result.

proc sql;

create table pairs2 as

select distinct A.city as city1, B.city as city2

from places as A inner join places as B

on A.city < B.city

order by a.city, b.city

select * from pairs2;

quit;

Super Contributor
Posts: 464

Re: Distinc pairs of cities using proc sql

Posted in reply to LarryWorley

sweet.

Thanks Larry,

thanks guys for your time and efforts

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
• 6 replies
• 308 views
• 1 like
• 4 in conversation