BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
julicny
Obsidian | Level 7

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. 

1 ACCEPTED SOLUTION

Accepted Solutions
SASKiwi
PROC Star

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

7 REPLIES 7
SASKiwi
PROC Star

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
Obsidian | Level 7

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. 

ballardw
Super User

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
Obsidian | Level 7

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
Calcite | Level 5
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
Obsidian | Level 7

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_

 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

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