topic Re: Extracting all records that share variable 2 values, where at least one variable 1 value is = XX in New SAS User

Extracting all records that share variable 2 values, where at least one variable 1 value is = XX

jibra — Mon, 21 Jul 2025 18:57:12 GMT

Here's a sample of what I have:

var1	var2
AA	Paul
BB	Paul
XX	Paul
dd	Mike
pp	Mike
cc	Dave
ee	Dave
ff	Dave
gg	Dave
XX	Dave
hh	Ken
ii	Ken
jj	Ken
XX	Ken
kk	Rick
ll	Rick
mm	Rick

Here's a sample of what I want:

var1	var2
AA	Paul
BB	Paul
XX	Paul
cc	Dave
ee	Dave
ff	Dave
gg	Dave
XX	Dave
hh	Ken
ii	Ken
jj	Ken
XX	Ken

Re: Extracting all records that share variable 2 values, where at least one variable 1 value is = XX

jibra — Mon, 21 Jul 2025 18:57:52 GMT

Any suggestion would be welcome.

Re: Extracting all records that share variable 2 values, where at least one variable 1 value is = XX

Quentin — Mon, 21 Jul 2025 19:39:35 GMT

Please post your sample data as code (DATA step with CARDS statement). Also please post the code you have tried. This will help others help you.

Re: Extracting all records that share variable 2 values, where at least one variable 1 value is = XX

Tom — Mon, 21 Jul 2025 20:10:58 GMT

Trying to guess what you mean it sounds like you want all observations for any person that has VAR1='XX'. (Note question are easier to read if the details are in the body of the question and not just the subject line.)

SQL should make that simple.

proc sql;
create table want as 
  select * from have
  where var2 in
    (select var2 from have where var1='XX')
;
quit;

Re: Extracting all records that share variable 2 values, where at least one variable 1 value is = XX

Kurt_Bremser — Mon, 21 Jul 2025 21:18:28 GMT

Double DO method:

data want;
do until (last.var2);
  set have;
  by var2 notsorted;
  if var1 = "XX" then flag = 1;
end;
do until (last.var2);
  set have;
  by var2 notsorted;
  if flag then output;
end;
drop flag;
run;

Re: Extracting all records that share variable 2 values, where at least one variable 1 value is = XX

Patrick — Mon, 21 Jul 2025 23:37:59 GMT

Or a data step hash table lookup

data want;
  if _n_=1 then
    do;
      dcl hash h1(dataset:'have(where=(var1=:"XX"))');
      h1.defineKey('var2');
      h1.defineDone();
    end;
  set have;
  if h1.check()=0;
run;

Re: Extracting all records that share variable 2 values, where at least one variable 1 value is = XX

mkeintz — Tue, 22 Jul 2025 13:56:26 GMT

If the dataset is not particularly big, then I think your best option is the hash object technique suggested by @Patrick. The code is simple.

That solution requires reading the data in two unsynchronized data streams, which could create a performance hit in the case of large datasets, due to disk activity.

Now if the data were sorted by VAR2, you could do:

data want;
  merge have (where=(var1='XX')  in=wanted) 
        have;
  by var2;
  if wanted;
run;

which synchronizes the data streams and reduces disk activity.

Your data are not sorted by VAR2, although it is grouped by VAR2. So you can generate data stream synchronization with code such as this:

data want (drop=_:);
  set have (where=(var1='XX') rename=(var2=_var2));

  do until (last.var2=1 and var2=_var2);
    set have;
    by var2 notsorted;
    if var2=_var2 then output; 
  end;
run;

The above assumes no more than one var1='XX' for any given VAR2. If you can have two or more var1='XX' cases, then:

data want (drop=_:);
  set have (where=(var1='XX') rename=(var2=_var2));
  by _var2 notsorted;

  if last._var2 then do until (last.var2=1 and var2=_var2);
    set have;
    by var2 notsorted;
    if var2=_var2 then output; 
  end;
run;

And yes, the degree of synchronization achieved would depend on the distribution of the "XX" cases.

Re: Extracting all records that share variable 2 values, where at least one variable 1 value is = XX

yabwon — Tue, 22 Jul 2025 07:35:07 GMT

Just to have (more) complete picture.

The power of IN operator over a macro variable with constant list of values:

data have;
input var1 $ var2 $;
cards;
AA	Paul
BB	Paul
XX	Paul
dd	Mike
pp	Mike
cc	Dave
ee	Dave
ff	Dave
gg	Dave
XX	Dave
hh	Ken
ii	Ken
jj	Ken
XX	Ken
kk	Rick
ll	Rick
mm	Rick
;
run;
proc print data=have;
run;

proc SQL noprint;
  select 
  distinct quote(var2)
  into :myList separated by " "
  from have
  where var1 = "XX"
  ;
quit;

data want;
  set have;
  where var2 in (&myList.);
run;
proc print data=want;
run;

BTW. Just for fun, here is an article "Route Sixty-Six to SAS Programming, or 63 (+3) Syntax Snippets for a Table Look-up Task, or How to Learn SAS by Solving Only One Exercise!" that provides 66 examples of solving the table-lookup task 🙂

All the best

Bart

Re: Extracting all records that share variable 2 values, where at least one variable 1 value is = XX

Ksharp — Tue, 22 Jul 2025 08:31:59 GMT

Or you would like to get it automatically ? and not type XX by hand ?

data have;
input var1 $ var2 $;
cards;
AA	Paul
BB	Paul
XX	Paul
dd	Mike
pp	Mike
cc	Dave
ee	Dave
ff	Dave
gg	Dave
XX	Dave
hh	Ken
ii	Ken
jj	Ken
XX	Ken
kk	Rick
ll	Rick
mm	Rick
;

proc sql;
create table want as
select * from have where var2 in 
(
select distinct var2 from have group by var1 having count(distinct var2)>1
)
;
quit;

Re: Extracting all records that share variable 2 values, where at least one variable 1 value is = XX

yabwon — Tue, 22 Jul 2025 08:54:32 GMT

I'm not sure if this is possible to have such data, but what about "Joe" (last record) ?

data have;
input var1 $ var2 $;
cards;
AA	Paul
BB	Paul
XX	Paul
dd	Mike
pp	Mike
cc	Dave
ee	Dave
ff	Dave
gg	Dave
XX	Dave
hh	Ken
ii	Ken
jj	Ken
XX	Ken
kk	Rick
ll	Rick
mm	Rick
AA	Joe
;

Bart

Re: Extracting all records that share variable 2 values, where at least one variable 1 value is = XX

Ksharp — Tue, 22 Jul 2025 09:21:40 GMT

yabwom,
I don't know either .
It is all depend on what the OP is looking for.
The code I posted is just guessing the intention of OP .

Re: Extracting all records that share variable 2 values, where at least one variable 1 value is = XX

jibra — Tue, 22 Jul 2025 15:30:42 GMT

All suggestions in this thread worked, but the hash object technique seems to be the most efficient. Thank you!

Re: Extracting all records that share variable 2 values, where at least one variable 1 value is = XX

jibra — Tue, 22 Jul 2025 15:41:24 GMT

Most efficient of all the solutions offered.