Filter on the basis of subcategories

[deega]

I have following data

Number	F	Count	Name
1234	2	1	OBC
1234	2	2	WWF
3456	3	1	ABC
3456	3	2	DEF
3456	3	3	WWF
5666	2	1	XYZ
5666	2	2	ABC
7888	3	1	WWF
7888	3	2	XYZ
7888	3	3	ABC

I want to filter it on the basis of subcategories Count >=2 & Name = 'WWF' and I need the whole group in the output. 

Number	F	Count	Name
1234	2	1	OBC
1234	2	2	WWF
3456	3	1	ABC
3456	3	2	DEF
3456	3	3	WWF

I wrote the following codes

data test;
set want;
by Number;
if count >=2 and Name = 'AWF';
run;

However with this I am not getting whole group in output rather I am getting only the records that are matching the given criteria.

Please guide.

[RahulG]

data HAVE;
input Number F Count Name $;
datalines;
1234 2 1 OBC
1234 2 2 WWF
3456 3 1 ABC
3456 3 2 DEF
3456 3 3 WWF
5666 2 1 XYZ
5666 2 2 ABC
7888 3 1 WWF
7888 3 2 XYZ
7888 3 3 ABC
;
proc sql;
create table Name_WWF as
select distinct Number 
from HAVE
WHERE NAME='WWF';

create table want as
select *
from have as outer 
where 
2 <= (select count(*)
from have as inner
where Number in (select number from Name_WWF )
and outer.number=inner.number 
group by number
);
QUIT;

first I found out Number that have 'WWF' and then I have used corelated query to get the desired result.

[Reeza · Accepted solution]

This is easier in SQL. I'm assuming count refers to the variable count not an aggregation. 

Proc SQL;
Create table want as
Select *
From have
where number in (select distinct number from have where name="WWF" and count>=2);
Quit;

[RahulG]

@Reeza

I want to learn how this count in where clause works.

What I understand Count(*) ( Aggregate function )  works with group by and having clause. 

[Ksharp]

data have;
infile cards expandtabs truncover;
input Number	F	Count	Name $;
cards;
1234	2	1	OBC
1234	2	2	WWF
3456	3	1	ABC
3456	3	2	DEF
3456	3	3	WWF
5666	2	1	XYZ
5666	2	2	ABC
7888	3	1	WWF
7888	3	2	XYZ
7888	3	3	ABC
;
run;
data want;
 do until(last.number);
  set have;
  by number;
 end;
 _c=count;_n=name;
 do until(last.number);
  set have;
  by number;
  if _c ge 2 and _n='WWF' then output;
 end;
 drop _:;
run;

[Ksharp]

If data like the following ,what do you expect ?


Number	F	Count	Name
1234	2	1	OBC
1234	2	2	WWF
1234        2      3      ABC  <-----------------
3456	3	1	ABC
3456	3	2	DEF
3456	3	3	WWF
5666	2	1	XYZ
5666	2	2	ABC
7888	3	1	WWF
7888	3	2	XYZ
7888	3	3	ABC
 

[deega]

@Ksharp

Actually F is 'Frequency', If there will be one more record of number '1234' then it will be like 

Number	F	Count	Name
1234	3	1	OBC
1234	3	2	WWF
1234    3       3    ABC

and in the output I would expect this record because 'WWF' existing at count = 2.

I need all the records of the group by Number where Name is 'WWF' with count=>2.

Thanks for all your support.