topic Re: data conversion error when persist SAS data to hive in SAS Data Management

data conversion error when persist SAS data to hive

zdk — Wed, 08 Jan 2020 02:15:29 GMT

When I persisted SAS data to hive,there was some dirty data in .sas7bdat file.Due to this error,the execution stopped.How can I deal with these dirty data?

data hivelib.cars (COLUMN_DELIMITER = '|');

set locallib.cars;

run;

Data conversion error on column 'xxx',

Re: data conversion error when persist SAS data to hive

Reeza — Wed, 08 Jan 2020 03:25:04 GMT

Depends on what type of 'dirty' and how they're dirty.

Please provide more details.

@zdk wrote:

When I persisted SAS data to hive,there was some dirty data in .sas7bdat file.Due to this error,the execution stopped.How can I deal with these dirty data?

data hivelib.cars (COLUMN_DELIMITER = '|');

set locallib.cars;

run;

Data conversion error on column 'xxx',

Re: data conversion error when persist SAS data to hive

zdk — Wed, 08 Jan 2020 03:51:50 GMT

thx

the error :data conversion error on column 'xxx' ,row xxx

note :due to errors above,SAS set option OBS=0,enabling syntax check mode.

This prevents execution of subsequent data modification statements.

dirty data is ....there were scrambled code or invisible characters in the source data...so failed to load...

My solution is to extract dirty data and process them seprately.But we have more than 3000 tables.It costs too much...Do you have any suggestions?

thank u very much!

Re: data conversion error when persist SAS data to hive

Reeza — Wed, 08 Jan 2020 04:02:08 GMT

First, ensure your encoding is set to the same across your two systems and is correct for your data. Then if it's the same, you have to clean your data. There really isn't any way around that.

However, the COMPRESS() function allows you remove invisible white characters. You could apply it to all your character strings and remove any invisible characters but that won't deal with the 'scrambled code', whatever that means.