Hello,
I have a really large data set on a sql server that I'm trying to download. The issue I'm having is the variable lengths are huge. Most variables have a length of 255 when perhaps 8 would be appropriate. How do I reformat the variables and simultaneously download the data? I tried the code below but get the following error message: "ERROR: Describe error: IColumnsInfo::GetColumnInfo failed. : Deferred prepare could not be completed.: Statement(s) could not be prepared.: Incorrect syntax near 'format'."
Code:
proc sql;
connect to oledb as codata (init_string="Provider=SQLOLEDB;Password=&pass.;Persist Security Info=True;User ID=&user.;
Initial Catalog=misc;Data Source=Sales" schema=dbo);
create table out.test as select * from connection to codata
(select
Idfield as Idfield format=10.
from
superlargetable
);
disconnect from codata;
quit;
Note when I remove the format=10. the code works fine. Thank you very much for any suggestions!
-Bill
If your main issue is simply to reduce the size of your SAS datasets read from SQL Server I've found using COMPRESS = cures the problem easily without resizing columns:
create table out.test (compress = yes or compress = binary) as select * from connection.....
I don't see in the code you provided this specific option, but maybe you have this turned on somewhere:
Specifying READ_LOCK_TYPE=NOLOCK results in the building of a query that uses incorrect syntax.
To avoid this problem, do not specify READ_LOCK_TYPE=NOLOCK. If you do not specify the option, the software can open the DBMS tables in the SAS® Explorer window.
Maybe it helps.
The format syntax is correct.
Anca.
You're doing a direct pass through SQL query there, which will require you to user the native SQL language. I don't recognize your DB so can't comment beyond that
I think you need dbsastype while loading variables which are huge in length from sql server. I've modified part of your syntax you can put it back to your main code to see if it works.
(select
Idfield
from
superlargetable (dbsastype=(Idfield='CHAR(10)'));
disconnect from codata;
quit;
If your main issue is simply to reduce the size of your SAS datasets read from SQL Server I've found using COMPRESS = cures the problem easily without resizing columns:
create table out.test (compress = yes or compress = binary) as select * from connection.....
Wow, thank you everyone for all the suggestions. I appreciate your thoughts.
I didn't specify the READ_LOCK_TYPE=NOLOCK option, so that wasn't the issue.
I tried the dbsastype syntax, but my query locked up.
Specifying (compress=yes) worked perfectly. On a sample of obs=1000 the data set was compressed by over 96%. I'm downloading the entire data set down right now. I estimate that the file will be less than 50gb, which will be a huge improvement
over what I would have ended up with. Thank you, SASKiwi!
Best
Bill
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.