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;
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;
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 ;
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;
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
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
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;
sweet.
Thanks Larry,
thanks guys for your time and efforts
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.