2

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:

sas column properties showing 4000 length and format

Matt Moehr
  • 123
  • 2
  • 10
  • FORMAT and variable LENGTH do not define the same attribute, although one can imply the other. – data _null_ Mar 28 '16 at 18:52
  • @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 Mar 28 '16 at 19:01
  • 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_ Mar 28 '16 at 20:00
  • Yes. The data step gives variables with more than 255 characters. – Matt Moehr Mar 28 '16 at 21:27
  • In your select clause, can you try `var2 length=4000,` instead of just `var2,` and see if it helps? – Dominic Comtois Mar 29 '16 at 05:38
  • @DominicComtois tried it. The column properties can be changed when I do `var2 format = $3999. length = 3998,` but the data is still cut off at 255 characters. – Matt Moehr Mar 29 '16 at 18:55
  • Puzzling... Maybe a shot in the dark, but what if you removed the `order by` and/or the `distinct` clauses? – Dominic Comtois Mar 30 '16 at 03:36
  • @DominicComtois removing the distinct clause works. Sigh. Just another reminder that the S in SQL stands for "structured" not "standard." Add it as an answer if you like. Thanks. – Matt Moehr Mar 30 '16 at 21:05
  • :) ok I'll add it. – Dominic Comtois Mar 30 '16 at 21:08

1 Answers1

2

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.

Dominic Comtois
  • 10,230
  • 1
  • 39
  • 61
  • 1
    Whoa. Please tell me this oddity only happens with PCFILES engine (punishment for reading from a M$ file?). Did someone report this as a bug to SAS tech support? – Quentin Mar 31 '16 at 00:32
  • 1
    I don't know. It might be a combination of factors. I'd encourage the OP to file a bug report for sure. – Dominic Comtois Mar 31 '16 at 01:04