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

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

1 ACCEPTED SOLUTION

Accepted Solutions
SASKiwi
PROC Star

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.....

View solution in original post

5 REPLIES 5
AncaTilea
Pyrite | Level 9

I don't see in the code you provided this specific option, but maybe you have this turned on somewhere:

38542 - A syntax error occurs when you specify the READ_LOCK_TYPE=NOLOCK option in a LIBNAME stateme...

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.

Reeza
Super User

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

stat_sas
Ammonite | Level 13

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;

SASKiwi
PROC Star

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.....

BillJones
Calcite | Level 5

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

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
  • 5 replies
  • 2166 views
  • 0 likes
  • 5 in conversation