Distinc pairs of cities using proc sql

Accepted Solution Solved
Reply
Super Contributor
Super Contributor
Posts: 444
Accepted Solution

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;

View solution in original post


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;

Respected Advisor
Posts: 4,934

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
Super Contributor
Posts: 444

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
Super Contributor
Posts: 444

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
  • 291 views
  • 1 like
  • 4 in conversation