Help using Base SAS procedures

Proc Datasets with where statement?

Accepted Solution Solved
Reply
Contributor
Posts: 68
Accepted Solution

Proc Datasets with where statement?

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


Accepted Solutions
Solution
‎08-26-2014 03:10 PM
Regular Contributor
Posts: 184

Re: Proc Datasets with where statement?

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.

View solution in original post


All Replies
Super User
Posts: 10,500

Re: Proc Datasets with where statement?

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?

Regular Contributor
Posts: 233

Re: Proc Datasets with where statement?

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

TaskOption
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 setsGENNUM=
Delete SAS files KILL
Specify the procedure input/output library LIBRARY=
Restrict processing to a certain type of SAS fileMEMTYPE=
Suppress the printing of the directoryNOLIST
Suppress error processingNOWARN
Provide read, write, or alter accessPW=
Provide read accessREAD=

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

Capture.JPG

Solution
‎08-26-2014 03:10 PM
Regular Contributor
Posts: 184

Re: Proc Datasets with where statement?

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.

Contributor
Posts: 68

Re: Proc Datasets with where statement?

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

Super User
Posts: 6,936

Re: Proc Datasets with where statement?

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Super User
Posts: 7,401

Re: Proc Datasets with where statement?

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;

Contributor
Posts: 68

Re: Proc Datasets with where statement?

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 Smiley Happy

Regards

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 383 views
  • 8 likes
  • 6 in conversation