Character length in data step versus proc sql

Question

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?

EDIT - adding some screen shots.

SAS thinks the variables are 4000 characters long:

FORMAT and variable LENGTH do not define the same attribute, although one can imply the other. — data _null_
@data_null_ yep. I know all about that. This is something else. The length and formats both say 4000, but character strings themselves end at 255. I could maybe do some screen grabs if that helps? — Matt Moehr
Do a test where you read the data into sas data set using data var2015; set mylib.var2015; run; See if that gives the VAR2 as you think it should be. — data _null_
Yes. The data step gives variables with more than 255 characters. — Matt Moehr
In your select clause, can you try var2 length=4000, instead of just var2, and see if it helps? — Dominic Comtois

Dominic Comtois Dominic Comtois · Accepted Answer · 2016-03-30T21:10:25

The culprit is the distinct clause. If you remove it, columns keep their lengths, and you can then eliminate duplicates with a proc sort if needed.

Character length in data step versus proc sql

1 Answers