CODEWITHSUNDEEP
saswhere-clausedatastep
Marco De Virgilis
Marco De Virgilis

Reputation: 1087

apply keep and where together sas

I am working with sas to manipulate some dataset. I am using the data step to apply some condition to keep columns and filter on some values. The problem is that I would like to filter on columns that in the end I will not need, so I would like to apply, first, the where clause, and then the keep clause. The problem is that sas executes, first the keep clause and then where, so when it is trying to apply the where instruction it doesn't find the columns on which it should be applied on. This is my code:

data newtable;
set mytable(where=(var1<value)
keep var2);
end;

In this case the error is the var1 cannot be found since I decided to keep only var2. I know I can do two data steps, but I would like to do everything in one step only. How can I achieve this?

Upvotes: 5

Views: 10409

Answers (2)

Amir
Amir

Reputation: 1000

This can be achieved by using the keep data set option on the output data set, e.g.(untested):

data newtable(keep=var2);
  set mytable(where=(var1<value));
end;

Alternatively a keep statement can be used, e.g. (untested):

data newtable;
  set mytable(where=(var1<value));
  keep var2;
end;

Upvotes: 4

DomPazz
DomPazz

Reputation: 12465

@Amir has the right of it. @Quentin is worried about the efficiency of it all. Unless your where clause is highly involved, then this will be your most efficient method.

data newtable;
  set mytable(where=(var1<value) keep=var1 var2);
  keep var2;
end;

Yes, var1 is read into the PDV, but you have limited the PDV to only the variables wanted in the output and needed in the where clause.

If you just want to run a where and do no other data step logic, then PROC SQL is just as efficient as the method above.

proc sql noprint;
create table newtable as
select var2
   from mytable
   where var1<value;
quit;

Upvotes: 1

Related Questions