Help using Base SAS procedures

Using PROC SQL to reformat variables

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 92
Accepted Solution

Using PROC SQL to reformat variables

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


Accepted Solutions
Solution
‎06-12-2014 11:15 PM
Super User
Posts: 3,252

Re: Using PROC SQL to reformat variables

Posted in reply to BillJones

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


All Replies
Super Contributor
Posts: 543

Re: Using PROC SQL to reformat variables

Posted in reply to BillJones

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.

Super User
Posts: 19,772

Re: Using PROC SQL to reformat variables

Posted in reply to BillJones

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

Trusted Advisor
Posts: 1,228

Re: Using PROC SQL to reformat variables

Posted in reply to BillJones

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;

Solution
‎06-12-2014 11:15 PM
Super User
Posts: 3,252

Re: Using PROC SQL to reformat variables

Posted in reply to BillJones

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

Frequent Contributor
Posts: 92

Re: Using PROC SQL to reformat variables

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 982 views
  • 0 likes
  • 5 in conversation