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.
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;
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.
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;
I want to learn how this count in where clause works.
What I understand Count(*) ( Aggregate function ) works with group by and having clause.
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;
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
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.