• Sign In
  1. Messages

Character Limits on SAS export from PostgreSQL

LabKey Support Forum (Inactive)

View Message

view list print
Character Limits on SAS export from PostgreSQL bront  2015-06-04 11:40
Status: Closed
 
hi,

One of our users has come across an issue when she exports PostgreSQL data into SAS.

A TEXT field in the database is listed as 2.15E9 in length when the SAS character maximum is 32767.

We've seen this before on VARCHAR fields without a limit, but I am not sure what to do with a TEXT field (where it appears there is no limit).

We're running LabKey 15.1, PostgreSQL 9.1, and she installed the latest SAS Client API Library today.

Many thanks,

bront
 
 
Jon (LabKey DevOps) responded:  2015-06-04 17:26
Hi Bront,

Although it looks like TEXT is unlimited in PostgreSQL, it does have a limit of 1GB, which is still huge.

http://www.postgresql.org/docs/current/interactive/datatype-character.html

And you are right, there is no equivalent datatype in SAS that works with TEXT from PostgreSQL due to the enormousness of TEXT.

http://support.sas.com/documentation/cdl/en/ds2ref/68056/HTML/default/viewer.htm#p00lvj8tbbkdozn1vwe0s7wgh5m8.htm

I'll have this looked into, but the only thing that I can personally think of is to import everything except for that one TEXT column, split that column across multiple columns with a varchar datatype, then import those into your DB. Kind of ugly, but effective.

I'll circle back with you on what I find out.

Regards,

Jon
 
Jon (LabKey DevOps) responded:  2015-06-04 23:16
Hi Bront,

So I've just confirmed that unfortunately there is no workaround for the character limit of SAS to allow TEXT datatypes from PostgreSQL to pass into SAS.

Beyond the splitting, it looks like we're out of options.

Regards,

Jon
 
bront responded:  2015-06-15 07:27
hi Jon,

Sorry it took me so long to write back (a week of vacation).

Thanks for looking into this and confirming what seemed to be the case.

cheers,

bront