Solved
Contributor
Posts: 41

# road name in all the city

HI ,

i have a data set like below , i have to check which road is there in all the city , for example "a" is there in all the city so we want the output to be like

 city road delhi a mumbai a chennai a bangalore a

the data is like below

data dsn;
infile cards dlm='09'x;
cards;
delhi a
delhi b
delhi c
mumbai d
mumbai a
chennai e
chennai f
chennai a
chennai g
bangalore i
bangalore j
bangalore k
bangalore a
;
run;

Accepted Solutions
Solution
‎02-08-2018 09:06 AM
Super User
Posts: 10,691

## Re: road name in all the city

``````data dsn;
infile cards;
cards;
delhi a
delhi b
delhi c
mumbai d
mumbai a
chennai e
chennai f
chennai a
chennai g
bangalore i
bangalore j
bangalore k
bangalore a
;
run;
proc sql;
select *
from dsn
having count(distinct city) eq (select count(distinct city) from dsn);
quit;``````

All Replies
Frequent Contributor
Posts: 86

## Re: road name in all the city

Just try a order by or where road eq 'x'

``````data dsn;
infile cards;
cards;
delhi a
delhi b
delhi c
mumbai d
mumbai a
chennai e
chennai f
chennai a
chennai g
bangalore i
bangalore j
bangalore k
bangalore a
;
run;

proc sql;
create table dsn_ordered as
select * from dsn
quit;``````
Solution
‎02-08-2018 09:06 AM
Super User
Posts: 10,691

## Re: road name in all the city

``````data dsn;
infile cards;
cards;
delhi a
delhi b
delhi c
mumbai d
mumbai a
chennai e
chennai f
chennai a
chennai g
bangalore i
bangalore j
bangalore k
bangalore a
;
run;
proc sql;
select *
from dsn
having count(distinct city) eq (select count(distinct city) from dsn);
quit;``````
Contributor
Posts: 56

## Re: road name in all the city

``````data dsn;
cards;
delhi a
delhi b
delhi c
mumbai d
mumbai a
chennai e
chennai f
chennai a
chennai g
bangalore i
bangalore j
bangalore k
bangalore a
;
run;
proc freq data=dsn;
data merg;