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
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.
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?
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
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.
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
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.
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;
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: