Hi @AndrewZ
Can you try running this macro against your zzz_unicode snowflake table
/****************************************************************************************************
File name: db_getmaxtextcolslengths.sas
Parent App:
File Type: Macro function
Purpose: Generate a SAS data containing the required SAS column lengths for a specified SnowFlake table
Inputs: p_inLibRef
p_inDbTableName
p_inDbSchema
p_inDbCatalog
p_outDsName
Outputs: Required SAS Columns lengths for specified snowflake table
****************************************************************************************************/
%MACRO db_getMaxTextColsLengths(
p_inLibRef=
, p_inDbTableName=
, p_inDbSchema=
, p_inDbCatalog=
, p_outDsName=);
%LOCAL
l_outLibName
l_outDsName
l_dbCat
l_dbSchema
l_dbTable
l_colNamesDS
;
%let l_outLibName = WORK;
%let l_outDsName = &p_outDsName;
%if (%INDEX(&p_outDsName, %STR(.)) GT 0) %then
%do;
%let l_outLibName = %SCAN(&p_outDsName,1,%STR(.));
%let l_outDsName = %SCAN(&p_outDsName,2,%STR(.));
%end;
/* Add required single quotes */
%let l_dbCat = %str(%')&p_inDbCatalog.%str(%');
%let l_dbSchema = %str(%')&p_inDbSchema.%str(%');
%let l_dbTable = %str(%')&p_inDbTableName.%str(%');
%let l_colNamesDS = WORK._tmp_col_names;
PROC SQL buffersize=50M noprint noerrorstop exec;
connect using &p_inLibRef as db;
CREATE TABLE &l_colNamesDS AS
SELECT
snow_col_len length=100 FORMAT=$100.
,sas_col_len length=200 FORMAT=$200.
,unicode_char_cnt length=200 FORMAT=$200.
,col_name
,col_type
FROM connection to db
(
SELECT
CASE WHEN DATA_TYPE <> 'TEXT' THEN ', '||TO_CHAR(COALESCE(NUMERIC_PRECISION,DATETIME_PRECISION,9),'FM999')||' ' ELSE
', MAX(LENGTH('||COLUMN_NAME||'))' END
|| ' AS snow_V' ||TO_CHAR(ORDINAL_POSITION,'FM999')||'_len' AS snow_col_len
,CASE WHEN DATA_TYPE <> 'TEXT' THEN ', 8' ELSE
', MAX(LENGTH('||COLUMN_NAME||') + (LENGTH(REGEXP_REPLACE('||COLUMN_NAME||', ''[\\x20-\\x7F]'', ''''))* 3))' END
|| ' AS sas_V' ||TO_CHAR(ORDINAL_POSITION,'FM999')||'_len' AS sas_col_len
,CASE WHEN DATA_TYPE <> 'TEXT' THEN ', 0' ELSE
', MAX(LENGTH(REGEXP_REPLACE('||COLUMN_NAME||', ''[\\x20-\\x7F]'', '''')))' END
|| ' AS V' ||TO_CHAR(ORDINAL_POSITION,'FM999')||'_unicode_char_cnt' AS unicode_char_cnt
,COLUMN_NAME as col_name
,DATA_TYPE as col_type
FROM
"&p_inDbCatalog"."INFORMATION_SCHEMA"."COLUMNS"
WHERE TABLE_CATALOG = &l_dbCat
AND TABLE_SCHEMA = &l_dbSchema
AND TABLE_NAME = &l_dbTable
/*AND DATA_TYPE = 'TEXT'*/
ORDER BY ORDINAL_POSITION
);
disconnect from db;
QUIT;
FILENAME dyncode TEMP;
DATA _NULL_;
FILE dyncode lrecl=300;
PUT '/* Data generatd Proc Sql call */';
PUT 'PROC SQL; ';
PUT +3 "CONNECT USING &p_inLibRef AS db; ";
PUT +3 "CREATE TABLE &p_outDsName AS ";
PUT +3 'SELECT * ';
PUT +3 'FROM connection to db ( ';
PUT +6 'SELECT ';
PUT +9 "'&p_inDbCatalog..&p_inDbSchema..&p_inDbTableName' AS Full_table_name ";
DO UNTIL (eof);
SET &l_colNamesDS end=eof;
PUT +9 snow_col_len ;
PUT +9 sas_col_len ;
PUT +9 unicode_char_cnt;
END;
PUT +6 'FROM "&p_inDbCatalog"."&p_inDbSchema"."&p_inDbTableName" ); ';
PUT +3 'disconnect from db; ';
PUT 'QUIT; ';
PUT ;
PUT "DATA &p_outDsName(KEEP=Full_table_name col_name col_type snow_col_len sas_col_len max_unicode_char_cnt) ; ";
PUT +3 "if (0) then SET &l_colNamesDS (KEEP=col_name col_type); ";
PUT +3 "SET &p_outDsName; ";
PUT +3 'RETAIN Full_table_name; ';
PUT +3 'Array saslens {*} 5 sas_: ; ';
PUT +3 'Array snowLens {*} 5 snow_: ; ';
PUT +3 'Array uniLens {*} 5 v: ; ';
PUT +3 'LENGTH snow_col_len sas_col_len max_unicode_char_cnt 5; ';
PUT;
PUT +3 'do _rec = 1 by 1 until(eof); ';
PUT +6 "SET &l_colNamesDS (KEEP=col_name col_type) end=eof; ";
PUT +6 'snow_col_len = snowLens[_rec]; ';
PUT +6 'sas_col_len = sasLens[_rec]; ';
PUT +6 'max_unicode_char_cnt = uniLens[_rec]; ';
PUT +6 'OUTPUT; ';
PUT +3 'end; ';
PUT +3 'STOP; ';
PUT 'RUN; ';
RUN;
%include dyncode;
%MEND db_getMaxTextColsLengths;
%db_getMaxTextColsLengths(
p_inLibRef=<SnowFlake-Libref>
, p_inDbCatalog=<SnowFlake-Catalog>
, p_inDbSchema=<SnowFlake-Schema>
, p_inDbTableName=zzz_unicode
, p_outDsName=<LIB.>SAS-DS-NAME)
Examine/Open the generated data set to see how long the SAS Character variable(s) have to be to properly represent the contents. Most definitely SAS variable(s) will require larger lengths.
Hope this helps,
Ahmed
... View more
