topic Re: Reading SQL Server Tables into SAS in SAS Data Management

Reading SQL Server Tables into SAS

julicny — Fri, 16 Dec 2016 21:01:36 GMT

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.

Re: Reading SQL Server Tables into SAS

SASKiwi — Fri, 16 Dec 2016 21:16:40 GMT

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;

Re: Reading SQL Server Tables into SAS

Kurt_Bremser — Fri, 16 Dec 2016 21:13:57 GMT

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

Re: Reading SQL Server Tables into SAS

ballardw — Fri, 16 Dec 2016 21:53:44 GMT

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.

Re: Reading SQL Server Tables into SAS

julicny — Mon, 19 Dec 2016 14:28:10 GMT

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!

Re: Reading SQL Server Tables into SAS

julicny — Mon, 19 Dec 2016 14:33:14 GMT

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.

Re: Reading SQL Server Tables into SAS

Kim_SOM — Tue, 10 Jan 2017 18:21:27 GMT

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.

Re: Reading SQL Server Tables into SAS

julicny — Tue, 10 Jan 2017 20:05:52 GMT

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_