topic Re: character length in proc sql versus data step in SAS Procedures

character length in proc sql versus data step

mattmoehr — Mon, 28 Mar 2016 18:51:23 GMT

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.

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

Re: character length in proc sql versus data step

mattmoehr — Mon, 28 Mar 2016 18:52:09 GMT

on SO: http://stackoverflow.com/questions/36268835/character-length-in-data-step-versus-proc-sql

Re: character length in proc sql versus data step

PGStats — Mon, 28 Mar 2016 22:23:36 GMT

Try telling SAS/SQL about the length you want:

proc sql noprint;
create table work.cbdata as
select
distinct var1,
var2 length=4000,
...

Re: character length in proc sql versus data step

ChrisNZ — Tue, 29 Mar 2016 05:58:29 GMT

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?

Re: character length in proc sql versus data step

Kurt_Bremser — Tue, 29 Mar 2016 08:01:18 GMT

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.

Re: character length in proc sql versus data step

mattmoehr — Tue, 29 Mar 2016 18:31:40 GMT

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

Re: character length in proc sql versus data step

mattmoehr — Tue, 29 Mar 2016 18:41:50 GMT

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

Re: character length in proc sql versus data step

mattmoehr — Tue, 29 Mar 2016 18:53:30 GMT

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.

Re: character length in proc sql versus data step

Paul_OldenKamp_org — Tue, 29 Mar 2016 18:59:35 GMT

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 SAS®- 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 with UTF-8 and DBCS encodings of SAS® data- 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

Re: character length in proc sql versus data step

mattmoehr — Tue, 29 Mar 2016 19:05:57 GMT

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.

Re: character length in proc sql versus data step

ChrisNZ — Wed, 30 Mar 2016 04:19:55 GMT

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

Re: character length in proc sql versus data step

mattmoehr — Wed, 30 Mar 2016 21:10:40 GMT

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=1#comment60222732_36268835)

And removing the distinct keyword works in my early testing. I have no explanation.

Re: character length in proc sql versus data step

PGStats — Wed, 30 Mar 2016 22:04:04 GMT

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

Re: character length in proc sql versus data step

ChrisNZ — Thu, 31 Mar 2016 00:54:33 GMT

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.

Re: character length in proc sql versus data step

PGStats — Thu, 31 Mar 2016 02:05:40 GMT

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

Re: character length in proc sql versus data step

ChrisNZ — Thu, 31 Mar 2016 02:40:03 GMT

It should.
Still a bug though, SAS should not ask for a sort that cannot take place. And still no reason for whichever application does this to truncate values.

Another way to know if the passthrough is the culprit is to use a where clause in the data step, if I am not mistaken.

Re: character length in proc sql versus data step

mattmoehr — Fri, 07 Jul 2017 12:24:43 GMT

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.

Re: character length in proc sql versus data step

PGStats — Thu, 31 Mar 2016 16:41:54 GMT

I did not suggest to use SQL pass through. I suggested to use option NOIPASSTHRU which prevents SAS/SQL to issue implicit pass through commands to MS-Access.

Re: character length in proc sql versus data step

Kurt_Bremser — Fri, 01 Apr 2016 05:46:34 GMT

@mattmoehr wrote:

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

But this is what actually happens. SAS hands the "distinct" over to Access so that the sorting is done within the database (default SAS behaviour when using ACCESS to Relational Databases). DATA steps don't do that, and forcing SAS to do the necessary sort by itself when using SQL should also get a clearer view on the problem.

Re: character length in proc sql versus data step

mattmoehr — Mon, 04 Apr 2016 16:54:24 GMT

Sorry, I misread your suggestion about implicit pass through. I will try to test that approach as it seems the safest/least likely to break when other software changes. The SAS tech suggested something about editting my registry for 'typeguessrows', but I don't think I want to put time into that approach because it requires admin privliges on each users' machine, which I may not have.

Is this the best documentation for NOIPASSTHRU?

... I was hoping there was a list of DB-specific functions that were implicitely passed. Does such a list exist anywhere?