topic Re: convert DATETIME16. numeric format to $9 character format in SAS Programming

convert DATETIME16. numeric format to $9 character format

Deep_Impact — Mon, 27 Jan 2020 16:49:11 GMT

data VITALS.vitals_1;
set VITALS.vitals_new;
CodeChar=put(code, datetime16.);
run;

Hi All,

I have a character variable "Code" in CSV file (eg : 123456-1) which gets automatically converted to DATETIME16. numeric format ( Informat : ANYDTDTM40.; eg 01JUN80:00:00:00) when I try importing the file.

Please suggest me ways to prevent it while importing or how to convert back the numeric DATETIME16. variable in the imported SAS file to the original $9 character variable.

I am using the following code to convert a DATETIME16. numeric format like 01JUN80:00:00:00 to get $9 character format like 8430-6)

data VITALS.vitals_1;
set VITALS.vitals_new;
CodeChar=put(code, datetime16.);

*/ "Code" is the numeric DATETIME16. variable which should needs be in character format like 8430-6 */;
run;

Thanks

Re: convert DATETIME16. numeric format to $9 character format

Reeza — Mon, 27 Jan 2020 16:55:56 GMT

Welcome to the SAS Community forums.

For this type of errors you should fix it in the IMPORT process, not after the fact because you cannot assume that SAS parsed it correctly at all.

I'm assuming you used PROC IMPORT or an IMPORT task to import the data? If so, check the log after, it will have the full code that was used. Take that code and modify the FORMAT/INFORMAT and INPUT statement for the variable in question. Change the format/informat to $9 instead. If you only have a few variables it may be easier to write a data step import yourself.

Another option is to add the GUESSINGROWS=MAX to your code to see if it fixes itself but it may not.

proc import out=vitals.vitals_new datafile="path to file" dbms=csv replace;
guessingrows=max;
run;

@Deep_Impact wrote:
data VITALS.vitals_1;
set VITALS.vitals_new;
CodeChar=put(code, datetime16.);
run;
Hi All,

I have a character variable "Code" in CSV file (eg : 123456-1) which gets automatically converted to DATETIME16. numeric format ( Informat : ANYDTDTM40.; eg 01JUN80:00:00:00) when I try importing the file.

Please suggest me ways to prevent it while importing or how to convert back the numeric DATETIME16. variable in the imported SAS file to the original $9 character variable.

I am using the following code to convert a DATETIME16. numeric format like 01JUN80:00:00:00 to get $9 character format like 8430-6)

data VITALS.vitals_1;
set VITALS.vitals_new;
CodeChar=put(code, datetime16.);

*/ "Code" is the numeric DATETIME16. variable which should needs be in character format like 8430-6 */;
run;

Thanks

Re: convert DATETIME16. numeric format to $9 character format

Deep_Impact — Mon, 27 Jan 2020 19:19:12 GMT

@Reeza : Thanks for your help; the guessingrows options worked in the proc import stage when I put a number like 100000. The guessingrows=max option was not allowing the datastep to stop.

Re: convert DATETIME16. numeric format to $9 character format

Reeza — Mon, 27 Jan 2020 19:33:02 GMT

It takes longer but should definitely stop. Once you have working code, take that from the log and run that next time. It's significantly faster because it can avoid the step of figuring out the data types.