topic Re: Help converting string datetime in CSV import in SAS Programming

Help converting string datetime in CSV import

RandoDando — Tue, 04 Apr 2023 12:24:28 GMT

I have a CSV file that I am importing, and would prefer to have the import statement saved so that it can run regularly to import updates to the CSV file. The file has a date variable stored as text in the format dd/mm/yyyy hh:mm (using 24 hr time). I would prefer to see the imported date as DDMMYYYY HH:MM AM/PM.

I am importing on a data step using the infile statement. Here is the gist of what I am running.

I get a blank column for date with the error: "Invalid argument to function INPUT".

Data Want;
infile "C:\My File.csv";
length code $12 date $19
input code $ date $ ;
date1 = input(date, Datetime19.);
drop date;
rename date1 = date;
run;
:

Re: Help converting string datetime in CSV import

PaigeMiller — Tue, 04 Apr 2023 12:30:16 GMT

Informat DATETIME19. doesn't work because the value is not in the appropriate format. From the documentation:

The datetime values must be in the following form: ddmmmyy or ddmmmyyyy, followed by a blank or special character, followed by hh:mm:ss.ss (the time).

You don't have that. I leave it as a homework assignment for you to understand what parts of your text string dd/mm/yyyy hh:mm do not match.

This does work:

data have;
    datetimestring='19/02/2023 13:22';
run;
data want;
    set have;
    date1=input(datetimestring,anydtdtm.);
    format date1 datetime19.;
run;

Note: some people do not like the ANYDTDTM. informat because it is a guessing format, and it could guess wrong. If that's a concern, then this will work.

data want;
    set have;
    date1=input(scan(datetimestring,1,' '),ddmmyy10.);
    time1=input(scan(datetimestring,2,' '),time5.);
    date=dhms(date1,hour(time1),minute(time1),0);
    format date datetime19.;
run;

Re: Help converting string datetime in CSV import

yabwon — Tue, 04 Apr 2023 12:27:10 GMT

Maybe like this:

Data Want;
infile cards dlm="|";
input code : $ 12. date ANYDTTME.;
format date DATEAMPM23.;
cards;
abc|01/02/2003 04:05
;
run;
proc print data=Want;
run;

Bart

Re: Help converting string datetime in CSV import

RandoDando — Tue, 04 Apr 2023 12:40:05 GMT

The first appears to have worked. I will compare to the original data to make sure.