SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Reading SQL Server Tables into SAS

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 13
Accepted Solution

Reading SQL Server Tables into SAS

[ Edited ]

I have started using SAS/ACCESS for ODBC to read SQL Server tables into SAS as datasets in a Windows PC environment.  Some columns come in a length 255 and I was looking for a straightforward way to reduce the lenght to something more reasonable.  For instance maybe the maximum bytes a variable actually needs is 25. How do I determine this value and set a length for the variable to accomidate this? Any other general tips on prepping data for use in SAS, given that it is already read in from SQL Server?

 

Thanks. 


Accepted Solutions
Solution
‎12-19-2016 09:24 AM
Super User
Posts: 3,102

Re: Reading SQL Server Tables into SAS

[ Edited ]

What works well for us is to use compression on all tables being read from SQL Server. That way we just let the column lengths default but compression removes all of the blank space so table sizes are still OK. Try both COMPRESS = YES and COMPRESS = BINARY to see what works best for you.

 

We actually have COMPRESS = BINARY on permanently for all SAS sessions - I understand this is quite a common practice.

 

If you want to determine the maximum length of a character field:

 

proc sql;
  select max(length(MyCharColumn)) from MyTable;
quit;

View solution in original post


All Replies
Solution
‎12-19-2016 09:24 AM
Super User
Posts: 3,102

Re: Reading SQL Server Tables into SAS

[ Edited ]

What works well for us is to use compression on all tables being read from SQL Server. That way we just let the column lengths default but compression removes all of the blank space so table sizes are still OK. Try both COMPRESS = YES and COMPRESS = BINARY to see what works best for you.

 

We actually have COMPRESS = BINARY on permanently for all SAS sessions - I understand this is quite a common practice.

 

If you want to determine the maximum length of a character field:

 

proc sql;
  select max(length(MyCharColumn)) from MyTable;
quit;
Occasional Contributor
Posts: 13

Re: Reading SQL Server Tables into SAS

All of our tables are small so I won't need to compress, but if I ever come across a larger table and this happens I'll keep it in mind.  The main initial problem I have is with printing.  There may also be other situations down the road where such a large length size is problematic.  So I'll figure out the variable length from the code you gave me and resize when necessary.  Thanks. 

Super User
Posts: 6,932

Re: Reading SQL Server Tables into SAS

In a data step, use a length statement before the set statement to override the length.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 10,483

Re: Reading SQL Server Tables into SAS

If your database has field sizes of 255 and are only holding 15 characters then it may be time to whip the DBA with wet noodles for laziness.

Occasional Contributor
Posts: 13

Re: Reading SQL Server Tables into SAS

I will likely need the DBA for future favors so whipping is not an option!  I thought that actually SAS might use a default of 255 if there is no information that comes over with the SQL Server table.  Thanks to everyone for their answers! 

New Contributor
Posts: 3

Re: Reading SQL Server Tables into SAS

A former co-worker found a macro referred to as the SQUEEZE macro. That calculated the length of the longest variable and set it to the length. I have not used it but it was very helpful in reducing the size of the datasets. The place that provided our data set everything to a character variable with the length of 250.
Occasional Contributor
Posts: 13

Re: Reading SQL Server Tables into SAS

Thanks for the tip.  I have downloaded the macro.  The latest version of the macro compresses character as well as numeric variables.  My datasets will be small enough that I will not need to compress the numeric variables.  It's nice to see that there is an option to compress only character variables...  NOCOMPRESS=_numeric_

 

 

☑ This topic is SOLVED.

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

Discussion stats
  • 7 replies
  • 597 views
  • 3 likes
  • 5 in conversation