Reading SQL Server Tables into SAS

[julicny]

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. 

[SASKiwi · Accepted solution]

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;

[julicny]

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. 

[Kurt_Bremser]

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

[ballardw]

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.

[julicny]

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! 

[Kim_SOM]

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.

[julicny]

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_