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

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
select
distinct var1,
var2,
...
from mylib.var2015
where
upcase(var1) = "DIQ"
order by var1
;
quit;

`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:


data vardb;
set mylib.var2015;
if var1 = "DIQ";
run;

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.

or

b) any documentation about how SAS processes the character lengths in the create table statements of proc sql?

1 ACCEPTED SOLUTION

Accepted Solutions
mattmoehr
Obsidian | Level 7

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.

View solution in original post

21 REPLIES 21
PGStats
Opal | Level 21

Try telling SAS/SQL about the length you want:

 

proc sql noprint;
create table work.cbdata as
select
distinct var1,
var2 length=4000,
...
PG
mattmoehr
Obsidian | Level 7

@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`?

mattmoehr
Obsidian | Level 7

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.

ChrisNZ
Tourmaline | Level 20

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?

Paul_OldenKamp_org
Obsidian | Level 7

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.

 

2012-05-08

46472 - Character strings can be truncated at 255 or 1024 characters when importing Excel files into...- Problem Note

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 ...

http://support.sas.com/kb/46/472.html, 25KB

2013-07-25

49017 - Character strings are truncated at 255 characters when you are using the ACCESSCS driver wit...- Problem Note

Character variables are truncated at 255 characters when you export data to MDB or ACCDB files using the ACCESSCS or PCFILES engines.

http://support.sas.com/kb/49/017.html, 25KB

 

Paul

mattmoehr
Obsidian | Level 7
The first one is for Excel and is using proc import so I'm not real sure what I could do with that. The second one sounds about right, but they said it was fixed in 9.3? Maybe there was a reversion? I sent in a problem to tech support.
Kurt_Bremser
Super User

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.

mattmoehr
Obsidian | Level 7

When I use a data step I get a character variable that is 389 characters long.

ChrisNZ
Tourmaline | Level 20

Please keep us posted. I am curious what tech support will say about different methods yielding different results.

mattmoehr
Obsidian | Level 7

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.

PGStats
Opal | Level 21

Could have something to do with a MS-Access limitation. MS-Access does not support sorting on Memo (ltext longer than 255 chars) fields.

PG
ChrisNZ
Tourmaline | Level 20

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.

 

PGStats
Opal | Level 21

If the bug, limitation, or feature is caused by MS-Access then proc SQL option NOIPASSTHRU should circumvent the problem, shouldn't it?

PG

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 21 replies
  • 118712 views
  • 16 likes
  • 5 in conversation