topic Re: Distinc pairs of cities using proc sql in SAS Programming

Distinc pairs of cities using proc sql

Tal — Mon, 22 Apr 2013 18:19:12 GMT

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;

Re: Distinc pairs of cities using proc sql

LarryWorley — Mon, 22 Apr 2013 18:29:33 GMT

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 ;

Re: Distinc pairs of cities using proc sql

DBailey — Mon, 22 Apr 2013 18:55:36 GMT

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;

Re: Distinc pairs of cities using proc sql

PGStats — Mon, 22 Apr 2013 19:04:07 GMT

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...

Re: Distinc pairs of cities using proc sql

Tal — Mon, 22 Apr 2013 19:45:37 GMT

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

Re: Distinc pairs of cities using proc sql

LarryWorley — Mon, 22 Apr 2013 19:56:43 GMT

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;

Re: Distinc pairs of cities using proc sql

Tal — Mon, 22 Apr 2013 20:01:38 GMT

sweet.

Thanks Larry,

thanks guys for your time and efforts