topic Re: Proc Datasets with where statement? in SAS Procedures

Proc Datasets with where statement?

SergioSanchez — Tue, 26 Aug 2014 16:10:08 GMT

Hi all

Actually I run the following peace of code to download a dataset from a libname (placed in an Oracle server)

data a;

set libnameA.mydataset (where =( datefield < date));

run;

I try;

proc datasets;

copy in= libnameA out= libnameB;

select mydataset (where =( datefield < date));

and this error ocurr;

ERROR 22-322: Syntax error, expecting one of the following: ALTER, ENCRYPTKEY, MEMTYPE, MT,

MTYPE, PROTECT, PW, READ, WRITE.

ERROR 76-322: Syntax error, statement will be ignored.

my questions are, Is there any way to perfomr this datastep with proc datasets?, Is there a better way than the one method I describe?

Thanks to all

Re: Proc Datasets with where statement?

ballardw — Tue, 26 Aug 2014 17:28:50 GMT

The Select clause is expecting a specific list of options and the dataset option Where is not one of them. So proc datasets won't let you do what you want in one step.

How do you define "better" in this case? Speed of execution, simplicity or brevity of code, something else?

Re: Proc Datasets with where statement?

Hima — Tue, 26 Aug 2014 18:19:11 GMT

The following options are available for proc datasets. There is no option for where statement.

Task		Option
Provide alter access to any alter-protected SAS file in the SAS library		ALTER=
Include information in the log about the number of observations, number of variables, number of indexes, and data set labels		DETAILS\|NODETAILS
Force a RUN group to execute even when there are errors		FORCE
Force an append operation		FORCE
Restrict processing for generation data sets		GENNUM=
Delete SAS files		KILL
Specify the procedure input/output library		LIBRARY=
Restrict processing to a certain type of SAS file		MEMTYPE=
Suppress the printing of the directory		NOLIST
Suppress error processing		NOWARN
Provide read, write, or alter access		PW=
Provide read access		READ=

Alternatively, the purpose can be acheived with the below code. I don't know how efficient it would be in your case but just a thought. Please keep in mind to drop the data set if this is a recurrence. I hope it helps.

Please adjust the code according to your needs.

data want;
input id temp $;
datalines;
1 101
1 102
1 201
2 203
3 302
;
run;

libname t '/sasuserhome/hyenigalla;

proc append
data=want
base=t.want( where=(id=1)) ;
run;

Log

28         proc append
29          data=want
30          base=t.want( where=(id=1)) ;
31         run;

NOTE: Appending WORK.WANT to T.WANT.
NOTE: BASE data set does not exist. DATA file is being copied to BASE file.
NOTE: There were 5 observations read from the data set WORK.WANT.
NOTE: The data set T.WANT has 3 observations and 2 variables.
NOTE: PROCEDURE APPEND used (Total process time):
real time 0.11 seconds
cpu time 0.00 seconds

Re: Proc Datasets with where statement?

Howles — Tue, 26 Aug 2014 19:10:16 GMT

WHERE filters inspect one observation at a time, but PROC DATASETS does not process observations. It only deals with metadata. In other words, specifying WHERE in a PROC DATASETS step doesn't make sense.

Re: Proc Datasets with where statement?

SergioSanchez — Wed, 27 Aug 2014 07:11:12 GMT

Thanks all for the comments and suggestions.

Hima I´d take a look at your code, thanks.

Ballardw, with "better" I mean less time of execution.

Regards

Re: Proc Datasets with where statement?

Kurt_Bremser — Wed, 27 Aug 2014 08:21:34 GMT

Actually, I can not think of anything faster than

data a;

set libnameA.mydataset (where =( datefield < date));

run;

This only depends on the read performance of the oracle source (and how it deals with the where condition) and the write performance of the disk where your WORK is allocated.

Re: Proc Datasets with where statement?

RW9 — Wed, 27 Aug 2014 08:29:50 GMT

Just as a thought, would doing the where on the oracle source first be quicker?

proc sql;

connect to oracle (path..);

create table A as

select * from connection to oracle

(select * from table where datefield < "ddmmyy");

disconnect from oracle;

quit;

Re: Proc Datasets with where statement?

SergioSanchez — Wed, 27 Aug 2014 11:39:20 GMT

RW9, I have installed the ODBC driver and it's too slow but thanks for your tip.

Ok, so data step it's not bad.

Thanks guys for your help

Regards