animal city
dog delhi
cat delhi
lion delhi
bear mumbai
cat mumbai
mouse mumbai
dog chennai
mouse chennai
bear chennai
output
animal city
delhi bear
delhi mouse
mumbai dog
mumbai lion
chennai cat
chennai lion
data have;
input animal :$5. city :$7.;
datalines;
dog delhi
cat delhi
lion delhi
bear mumbai
cat mumbai
mouse mumbai
dog chennai
mouse chennai
bear chennai
;
run;
proc sql;
create table want as
select a.*
from (select * from (select distinct animal from have),(select distinct city from have)) as a
natural left join have as b
where b.animal is missing;
quit;
data a;
input animal $ city $;
datalines;
dog delhi
cat delhi
lion delhi
bear mumbai
cat mumbai
mouse mumbai
dog chennai
mouse chennai
bear chennai
;
run;
Output
data b;
input animal $ city $;
datalines;
bear delhi
mouse delhi
dog mumbai
lion mumbai
cat chennai
lion chennai
;
run;
Do not post the same question twice. All replies should go to your other thread at https://communities.sas.com/t5/SAS-Programming/need-those-animal-which-are-not-in-city/td-p/773036/
I really love the PROC FREQ `sparse` option for these. You can do it in PROC SQL, too, but I'm not the best PROC SQL programmer.
data have;
input animal :$5. city :$7.;
datalines;
dog delhi
cat delhi
lion delhi
bear mumbai
cat mumbai
mouse mumbai
dog chennai
mouse chennai
bear chennai
;
run;
proc freq
data = have;
tables animal * city / list sparse out = want (keep = animal city count where = (count = 0));
run;
Obs animal city COUNT 1 bear delhi 0 2 cat chennai 0 3 dog mumbai 0 4 lion chennai 0 5 lion mumbai 0 6 mouse delhi 0
@maguiremq I wouldn't worry about not seeing the SQL solution. It sure wasn't readily apparent to me! After futzing with it, I decided if you made a cartesian list of distinct cities and animals you should be able to left join that to the data to see what is missing.
proc sql;
create table animals as
select distinct animal
from have;
create table cities as
select distinct city
from have;
create table cartesianlist as
select a.city, b.animal
from cities a, animals b;
select a.city, a.animal
from cartesianlist a
left join have b on a.city = b.city and a.animal = b.animal
where b.animal is null
order by a.city, a.animal;
quit;
Output is
city | animal |
---|---|
chennai | cat |
chennai | lion |
delhi | bear |
delhi | mouse |
mumbai | dog |
mumbai | lion |
I like the sparse thing better. Never used that before.
Edit: The web says "the SPARSE option provides 'all possible combinations of
levels of the variables in the table, even when some
combination levels do not occur in the data.'
All possible combinations is essentially a Cartesian join. So that makes sense.
data have;
input animal :$5. city :$7.;
datalines;
dog delhi
cat delhi
lion delhi
bear mumbai
cat mumbai
mouse mumbai
dog chennai
mouse chennai
bear chennai
;
run;
proc sql;
create table want as
select a.*
from (select * from (select distinct animal from have),(select distinct city from have)) as a
natural left join have as b
where b.animal is missing;
quit;
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.