Hello all,
I am a beginner of SAS and processing a large data set. Let me show my problem by an example, there are three groups A, B and C. and each group have different number (or names) such as
Group Member;
A 1
A 1
A 2
B 1
B 1
B 1
C 2
C 1
I would like to exclude Group A and C from my sample, as there are two different members (i.e., 1 and 2) in same group. And include Group B in my sample, as all member in Group B are same.
I really want to show some codes, but I have no idea about it. Could you please give me some suggestions about it?
Many thanks in advance.
Best regards,
France
Hi,
You could try sorting the data and then use by-group processing:
proc sort data = have;
by group member;
run;
data want(drop = member_count);
member_count = 0;
do until(last.group);
set have;
by group member;
member_count = member_count + first.member;
end;
do until(last.group);
set have;
by group member;
if member_count = 1 then
output;
end;
run;
The first do loop checks how many different members there are for the group being processed and then the second loop outputs the data if there was only one member for the same group.
Regards,
Amir.
With proc sql :
data have;
input Group $ Member;
cards;
A 1
A 1
A 2
B 1
B 1
B 1
C 2
C 1
;
run;
proc sql;
CREATE TABLE want AS
SELECT *
FROM HAVE
GROUP BY Group
HAVING count(DISTINCT Member)=1;
quit;
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.