topic Re: Where statement with IN operator in SAS Procedures

Where statement with IN operator

MARS — Fri, 10 Jun 2011 16:17:19 GMT

Hi

Actually I want to sub set my data with the following program

data final.abc;
set abc;
where date = '30Dec2010" d and customerNo in (select custno. from salesdata);
run;

data set abc and and data set salesdata both exist in as a temporary data set.
I want to sub set the data not only by date (a variable in dataset abc) but also by CustomerNo( also a variable in data set abc) by setting its value equal to Custno. ( a variable in another dataset salesdata).

Can somebody please check the syntax for sas, as I am unable to run this command.

regards
Mars

Re: Where statement with IN operator

Paige — Fri, 10 Jun 2011 16:20:26 GMT

SELECT clause will not work in a Data step.

If you want to use the SELECT clause, you have to do this in PROC SQL.

Re: Where statement with IN operator

sbb — Fri, 10 Jun 2011 16:53:29 GMT

The SAS PROC SQL documentation has examples for generating a macro variable data-string for a WHERE IN () as you have suggested.

Scott Barry
SBBWorks, Inc.

Suggested Google advanced search argument, this topic / post:

generate where statement in string using proc sql select into site:sas.com

Re: Where statement with IN operator

SPR — Fri, 10 Jun 2011 19:23:47 GMT

Hello Mars,

You need something like this:
[pre]
proc SQL;
create table final.abc as
select a.*,b.* /* list all variables you need from two datasets */
from abc as a, salesdata as b
where a.date='30Dec2010'd and a.customerNo=b.Custno
;quit;
[/pre]
Sincerely,
SPR