Help using Base SAS procedures

character length in proc sql versus data step

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 11
Accepted Solution

character length in proc sql versus data step

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?


Accepted Solutions
Solution
‎07-07-2017 08:23 AM
Occasional Contributor
Posts: 11

Re: character length in proc sql versus data step

[ Edited ]

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


All Replies
Occasional Contributor
Posts: 11

Re: character length in proc sql versus data step

Respected Advisor
Posts: 4,641

Re: character length in proc sql versus data step

Try telling SAS/SQL about the length you want:

 

proc sql noprint;
create table work.cbdata as
select
distinct var1,
var2 length=4000,
...
PG
Occasional Contributor
Posts: 11

Re: character length in proc sql versus data step

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

Occasional Contributor
Posts: 11

Re: character length in proc sql versus data step

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.

PROC Star
Posts: 1,558

Re: character length in proc sql versus data step

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?

Contributor
Posts: 20

Re: character length in proc sql versus data step

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

Occasional Contributor
Posts: 11

Re: character length in proc sql versus data step

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.
Super User
Posts: 6,928

Re: character length in proc sql versus data step

[ Edited ]

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 11

Re: character length in proc sql versus data step

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

PROC Star
Posts: 1,558

Re: character length in proc sql versus data step

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

Occasional Contributor
Posts: 11

Re: character length in proc sql versus data step

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.

Respected Advisor
Posts: 4,641

Re: character length in proc sql versus data step

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
PROC Star
Posts: 1,558

Re: character length in proc sql versus data step

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.

 

Respected Advisor
Posts: 4,641

Re: character length in proc sql versus data step

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

PG
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 21 replies
  • 5769 views
  • 8 likes
  • 5 in conversation