Blob fields in SAS are truncated
I was working on a SAS job that retrieves a table from a SQL server and then loads that table into an Oracle table.
One of the fields on the SQL Server is blob and they can be 1G in size. I get length warnings when I run these blobs in the oracle table, it seems to be truncated and the files there are corrupted as a result.
I've seen SAS state that the character variable can be max 32KB, but SAS also states it can access blocks up to 2G.
How can we achieve this?
proc sql;
create view work.W2K3NU8 as
select
ID,
DNUMBER,
FILENAME,
FILE
format = $HEX2048.
informat = $HEX2048.,
(input(compress(DATEENTERED),YYMMDD10.)) as DATEENTERED length = 8
format = date.
informat = date.
label = 'DATEENTERED',
(input(compress(DATEADDED),YYMMDD10.)) as DATEADDED length = 8
format = date.
informat = date.
label = 'DATEADDED',
(input(compress(DATECHANGED),YYMMDD10.)) as DATECHANGED length = 8
format = date.
informat = date.
label = 'DATECHANGED',
TYPE
from &SYSLAST;
quit;
And here is the data step
data trd.GAFILES
(dbnull = (
ID = NO
DNUMBER = YES
FILENAME = YES
GA_FILE = YES
DATEENTERED = YES
DATAADDED = YES
DATECHANGED = YES
TYPE = YES
ETL_CREATE = YES
ETL_UPDATE = YES));
attrib ID length = $255
format = $255.
informat = $255.
label = 'ID';
attrib DNUMBER length = $10
format = $10.
informat = $10.
label = 'DNUMBER';
attrib FILENAME length = $255
format = $255.
informat = $255.
label = 'FILENAME';
attrib GA_FILE length = $4096
format = $HEX2048.
informat = $HEX2048.
label = 'GA_FILE';
attrib DATEENTERED length = 8
format = DATETIME20.
informat = DATETIME20.
label = 'DATEENTERED';
attrib DATAADDED length = 8
format = DATETIME20.
informat = DATETIME20.
label = 'DATAADDED';
attrib DATECHANGED length = 8
format = DATETIME20.
informat = DATETIME20.
label = 'DATECHANGED';
attrib TYPE length = $100
format = $100.
informat = $100.
label = 'TYPE';
attrib ETL_CREATE length = 8
format = DATETIME20.
informat = DATETIME20.
label = 'ETL_CREATE';
attrib ETL_UPDATE length = 8
format = DATETIME20.
informat = DATETIME20.
label = 'ETL_UPDATE';
call missing(of _all_);
stop;
run;
a source to share
SAS datasets do not support character size 32767. I'm not sure where you've seen it support more; you are probably reading the ACCESS link describing the different DBMS data types (for example, in the DB2 section, it describes BLOBs and CLOBs as resolutions up to 2GB, but describes what DB2 supports - not SAS support).
SAS will happily access BLOBs, but there will be no more than 32767 of it. You will need to read snippets or use a DBMS specific language in a session through a session (which should go through it without touching it). You can read snippets like this (fill in the appropriate substring function and connection information):
proc sql;
connect to <>;
create table SASTBL as
select * from connection to <> (
select substring_Function(blobfield,1,32767) as blob_1,
substring_Function(blobfield,32768,32767) as blob_2,
substring_Function(blobfield,65535,32767) as blob_3,
(... etc ... )
from your_tbl;
);
quit;
If you have 9.4, you can also use FedSQL to do the conversion; I'm not very familiar with FedSQL, but its goal is to support more datatypes than SAS support. It doesn't explicitly say it can support BLOBs (notes on BLOBs consistently map to a similar datatype, which presumably means char or varchar), but it might be worth it if you have 9.4.
a source to share