topic Re: Using PROC SQL to reformat variables in SAS Procedures

Using PROC SQL to reformat variables

BillJones — Fri, 13 Jun 2014 01:32:14 GMT

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

Re: Using PROC SQL to reformat variables

AncaTilea — Fri, 13 Jun 2014 02:18:49 GMT

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 statement in SAS/ACCESS® Interface to OLE DB

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.

Re: Using PROC SQL to reformat variables

Reeza — Fri, 13 Jun 2014 02:23:32 GMT

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

Re: Using PROC SQL to reformat variables

stat_sas — Fri, 13 Jun 2014 02:40:43 GMT

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;

Re: Using PROC SQL to reformat variables

SASKiwi — Fri, 13 Jun 2014 03:15:22 GMT

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

Re: Using PROC SQL to reformat variables

BillJones — Fri, 13 Jun 2014 06:39:44 GMT

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