BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
SergioSanchez
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
Howles
Quartz | Level 8

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

7 REPLIES 7
ballardw
Super User

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?

Hima
Obsidian | Level 7

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

Howles
Quartz | Level 8

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.

SergioSanchez
Calcite | Level 5

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

Kurt_Bremser
Super User

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

SergioSanchez
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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