topic Help with WHERE statement in SAS Programming

Help with WHERE statement

marleeakerson — Mon, 13 Jul 2020 23:24:03 GMT

Hello,

I am trying to write a statement to include only certain variables. Take the sample data below:

ID Index

1 A

1 B

2 A

3 A

3 B

So in this instance, I am only trying to include the IDs when they only have an A Index, and no B Index. So using this sample data I would only want ID #2 because that is the only ID that doesn't also have a B Index.

Does this make sense? How can I code for this?

Thank you.

Re: Help with WHERE statement

Shmuel — Mon, 13 Jul 2020 23:51:52 GMT

proc sql;

create table temp select distinct ID from have

where index='B;

create table want as select * from have

where ID not in (select ID from temp);

quit;

Re: Help with WHERE statement

ketpt42 — Mon, 13 Jul 2020 23:54:36 GMT

This should be easy to accomplish with SQL. If your dataset is huge, there might be more efficient ways to do it.

data have;
    infile datalines;
    input ID :8. Index $1.;
datalines;
1 A
1 B
1 B
2 A
3 A
3 B
;
run;
 
proc sql;
create table want as
select id
  from have 
 where index='A'
   and id not in (SELECT id from have where Index = 'B')
 order by id;
quit;

ID
2

Re: Help with WHERE statement

mkeintz — Tue, 14 Jul 2020 01:33:27 GMT

If the data are sorted by ID, then:

data have;
    infile datalines;
    input ID :8. Index $1.;
datalines;
1 A
1 B
1 B
2 A
3 A
3 B
run;

data want (drop=_:);
  set have (where=(index^='A'))
      have (where=(index='A'));
  by id;
  if first.id=1 and index='A' then _keep+1;
  else if first.id then _keep=0;
  if _keep;
run;

Re: Help with WHERE statement

PGStats — Tue, 14 Jul 2020 01:50:44 GMT

Proc SQL with autoremerge provides this simple solution:

proc sql;
create table want as
select *
from have
group by ID
having sum(Index ne "A") = 0;
quit;

Re: Help with WHERE statement

Kurt_Bremser — Tue, 14 Jul 2020 06:20:45 GMT

The fastest method would be a data step, if your data is already sorted:

data want;
merge
  have
  have (
    in=delete
    keep=id index
    rename=(index=_i)
    where=(_i = 'B')
  )
;
by id;
if not delete;
drop _i;
run;

If it's not sorted, a hash table can do it:

data want;
set have;
if _N_ = 1
then do;
  declare hash h (dataset:"have (where=(index = 'B'))");
  h.definekey("id");
  h.definedone();
end;
if h.check() ne 0;
run;

The hash declaration will automatically discard duplicate entries for ID, so the in-memory table should be fairly small.

Both methods should outperform SQL if the table is quite large.

Re: Help with WHERE statement

Ksharp — Tue, 14 Jul 2020 11:59:46 GMT

Same as PG's code:

data have;
infile datalines;
input ID :8. Index $1.;
datalines;
1 A
1 B
1 B
2 A
3 A
3 B
;
run;

proc sql;
create table want as
select * from have
group by id
having count(*)=sum(Index='A');
quit;