03-28-2016 02:51 PM
Is it possible that creating a table with SAS proc sql will truncate a character variable at 255 characters? Does it have to do with the library engine type?
I'm using a PCFILES libname like this (note the dbmax_text option):
libname mylib PCFILES PATH = "C:\path\to\my\32bit\MS Acccess.ACCDB" dbmax_text=4000;
Then I do a proc sql like this:
proc sql noprint;
create table work.cbdata as
upcase(var1) = "DIQ"
order by var1
`var2` is getting truncated at 255 even though it's format is listed as `$4000.` in the column property view. When I do this as a data step, all of the character data comes through:
if var1 = "DIQ";
I have 40-50 sql procedures like this embedded in my macro library, so I would prefer not to refactor all the macros to use data steps (if that's even possible). Does anyone know either
a) if there is some option in proc sql to make it respect the dbmax_text.
b) any documentation about how SAS processes the character lengths in the create table statements of proc sql?
03-31-2016 12:18 PM - last edited on 07-07-2017 08:24 AM by ChrisHemedinger
Editor's note: This question/challenge is specific to using PROC SQL with Microsoft Access and the SAS/ACCESS to PC Files engine.
@PGStats I'm not going to start using passthrough. Then my macros would be dependent on MS Access-specific sql, which is worse than the current situation. If I have to do a re-write, I'm going to move this into data steps.
@ChrisNZ The data steps work just fine with either the if statement or the where statement to select rows.
I've taken out the sort from SQL statement, but that does not seem to have any effect.
I think it all comes down to how the distinct clause is implemented. Apparently, it is only wants to "distinct-ify" the first 255 characters in a DB field, so it only spits out the first 255 chars? I dunno. I've removed the distinct clause from my production macro and the first use case, seems to work with our desired behavior.
03-28-2016 06:23 PM
Try telling SAS/SQL about the length you want:
proc sql noprint; create table work.cbdata as select distinct var1, var2 length=4000, ...
03-29-2016 02:41 PM
@PGStats Good tip, but it doesn't seem to have any effect. This is in a `create table...as...select` clause so I'm not sure the length statement is even getting read. Maybe if I did a separate `create table` + `insert into ... select ... from`?
03-29-2016 02:53 PM
I did this
... var2 format = $3999. length = 3998, ...
And those numbers show up in the column properties, so I think they are getting applied. However, the character string is still cut off at 255. I'll try contacting tech support.
03-29-2016 01:58 AM
This looks like a bug.
There is no reason proc sql should retrieve data that's different from what a data step fetches.
What does tech support say?
03-29-2016 02:59 PM
There are some SAS Notes that may ge relavant and have some things to try. They don't fit this problem exactly, Excel instead of Access and export instead of import.
Character strings might be truncated at 255 or 1024 characters when importing Excel files into SAS. This can have several causes including the number of rows scanned by the Microsoft Access Connectivity Engine, the ...
Character variables are truncated at 255 characters when you export data to MDB or ACCDB files using the ACCESSCS or PCFILES engines.
03-29-2016 03:05 PM
03-29-2016 03:57 AM - edited 03-29-2016 04:01 AM
This is a limit set by MS Access. See http://webcheatsheet.com/sql/access_specification.php
You would need to somehow persuade Access that this is a "memo" field.
Edit: I see that you are not writing to Access, but reading from it. Still that 255 looks suspiciously like a MS Access limit that somehow comes into play.
03-30-2016 12:19 AM
Please keep us posted. I am curious what tech support will say about different methods yielding different results.
03-30-2016 05:10 PM
Over on SO, commenter recommended remove distinct and/or order by. (http://stackoverflow.com/questions/36268835/character-length-in-data-step-versus-proc-sql?noredirect...
And removing the distinct keyword works in my early testing. I have no explanation.
03-30-2016 06:04 PM
Could have something to do with a MS-Access limitation. MS-Access does not support sorting on Memo (ltext longer than 255 chars) fields.
03-30-2016 08:54 PM
Even if that's the reason, it's still a bug, isn't it?
1- Sorting has nothing to do with variable length, so it's a bad way to surface this limitation regardless at what end this happens
2- SAS should know this limitation and not send the order by clause in the pass-thru code
This could explain a different the result in the data step as an if statement is used there and not a where statement.
03-30-2016 10:05 PM
If the bug, limitation, or feature is caused by MS-Access then proc SQL option NOIPASSTHRU should circumvent the problem, shouldn't it?
Need further help from the community? Please ask a new question.