DATA Step, Macro, Functions and more

road name in all the city

Accepted Solution Solved
Reply
Contributor
Posts: 41
Accepted Solution

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  

 

cityroad
delhia
mumbaia
chennaia
bangalorea

 

the data is like below 

 

data dsn;
infile cards dlm='09'x;
input city$ road$;
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

Posted in reply to soham_sas
data dsn;
infile cards;
input city :$20. road $;
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
  group by road
    having count(distinct city) eq (select count(distinct city) from dsn);
quit;

View solution in original post


All Replies
Frequent Contributor
Posts: 86

Re: road name in all the city

Posted in reply to soham_sas

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

data dsn;
infile cards;
input city$ road$;
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
order by road, city;
quit;
Solution
‎02-08-2018 09:06 AM
Super User
Posts: 10,691

Re: road name in all the city

Posted in reply to soham_sas
data dsn;
infile cards;
input city :$20. road $;
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
  group by road
    having count(distinct city) eq (select count(distinct city) from dsn);
quit;
Contributor
Posts: 56

Re: road name in all the city

Posted in reply to soham_sas
data dsn;
input city$ road$;
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;
table road/out=dsn1;run;
proc sort data=dsn1;by road;run;
proc sort data=dsn;by road;run;
data merg;
merge dsn dsn1;by road;run;
data want(keep=road city);
set merg;
if count=4;run;
☑ This topic is solved.

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

Discussion stats
  • 3 replies
  • 98 views
  • 0 likes
  • 4 in conversation