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;
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;
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;
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;
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;
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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.