Hello,
I am looking for how to find two different variables but only if they exist within the same group. For example I want a list of groups where both A and B exist together. In the example below, that would be Group 1, Group 3, and Group 4. Thank you!
Group | Code |
1 | C |
2 | B |
3 | B |
4 | B |
5 | C |
3 | A |
1 | B |
4 | A |
5 | B |
1 | B |
@sashelp123 wrote:
I am looking for how to find two different variables but only if they exist within the same group.
Do you mean you want two observations if two different values of CODE exists in the same group?
For example I want a list of groups where both A and B exist together. In the example below, that would be Group 1, Group 3, and Group 4.
I don't see how Group 1 meets this criterion.
Show us the desired output
So is that the entire question, A and B in the same group?
Or is the question really like this:
What groups have both A and B; and what groups have both A and C, and so on?
Untested:
proc sort data=have out=sorted nodupkey;
by group code;
run;
data want;
set sorted;
by group;
if not first.group and code = 'B' and lag(code) = 'A';
keep group;
run;
EDIT: Simplified the code.
UNTESTED CODE
proc sort data=have;
by group;
run;
proc transpose data=have out=have_t;
by group;
id code;
run;
data want;
set have_t;
if whichc('A',of col:)>0 and whichc('B',of col:)>0;
run;
If you want tested code, please provide the data as SAS data step code (instructions) and not as screen captures.
proc sort data=have;
by group;
run;
data want;
do until (last.group);
set have;
by group;
if code = "A" then _a = 1;
if code = "B" then _b = 1;
end;
if _a and _b;
keep group;
run;
data have;
input Group Code $;
cards;
1 C
2 B
3 B
4 B
5 C
3 A
1 B
4 A
5 B
1 B
;
proc sql;
create table want as
select * from have
group by group
having sum(code='A') ne 0 and sum(code='B') ne 0;
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 16. 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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: