topic Re: Informat CSV Date with type "2017-01-01 00:00:00" in New SAS User

Informat CSV Date with type "2017-01-01 00:00:00"

Dynamike — Wed, 14 Nov 2018 09:23:17 GMT

Hi all,

a csv data file consists of a variable "date" with the following type "2017-01-01 00:00:00"

How can import such an type. I don't need the information of time "00:00:00"

So MMDDYY10. would be fine, but it does not work. The result is a missing value.

Thank you for your help!

Mike

Re: Informat CSV Date with type "2017-01-01 00:00:00"

RW9 — Wed, 14 Nov 2018 09:41:03 GMT

Use the format ymddttm to read in the data, then datepart() that, e.g.:

http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a003172040.htm

Re: Informat CSV Date with type "2017-01-01 00:00:00"

Kurt_Bremser — Wed, 14 Nov 2018 09:42:06 GMT

You are using the wrong informat. MMDDYY expects a MDY order, but you have a YMD order in your data.

Use YYMMDD instead:

data test;
instring = "2017-01-01 00:00:00";
date_value = input(instring,yymmdd10.);
format date_value yymmddd10.;
run;

Re: Informat CSV Date with type "2017-01-01 00:00:00"

Dynamike — Wed, 14 Nov 2018 11:01:42 GMT

thank you! That works!

But, if there is more than one instring e.g.

"2017-01-01 00:00:00"

"2018-03-25 00:00:00"

"2016-05-03 00:00:00"

my solution would be:

DATA Test_Out;
LENGTH tod_datum $ 10;

FORMAT tod_datum yymmddd10.;

INFORMAT tod_datum yymmddd10.;

INFILE 'C:\Test_In.csv'
delimiter=','
MISSOVER
firstobs=2
DSD
LRECL=32767;
INPUT tod_datum : ?? $CHAR23.
RUN;

Data test_out;
Set test_out;
todtag=input(tod_datum,yymmdd10.);
format todtag ddmmyy10.;
run;

This works, but is there no direct way? informat not as a string, but another date format (E8601DTw.d seems to be similar, but does not work).
Or is there another way to avoid the second data step?

Thank u for ur effort!

Mike

Re: Informat CSV Date with type "2017-01-01 00:00:00"

ballardw — Wed, 14 Nov 2018 16:26:15 GMT

@Dynamike wrote:
thank you! That works!

But, if there is more than one instring e.g.

"2017-01-01 00:00:00"

"2018-03-25 00:00:00"

"2016-05-03 00:00:00"

my solution would be:

DATA Test_Out;
LENGTH tod_datum $ 10;

FORMAT tod_datum yymmddd10.;

INFORMAT tod_datum yymmddd10.;

INFILE 'C:\Test_In.csv'
delimiter=','
MISSOVER
firstobs=2
DSD
LRECL=32767;
INPUT tod_datum : ?? $CHAR23.
RUN;

Data test_out;
Set test_out;
todtag=input(tod_datum,yymmdd10.);
format todtag ddmmyy10.;
run;

This works, but is there no direct way? informat not as a string, but another date format (E8601DTw.d seems to be similar, but does not work).
Or is there another way to avoid the second data step?

Thank u for ur effort!

Mike

Why did you go to the trouble of setting a character length and then assigning numerical informat and formats and then forcing it to be read as character? Also the informat you want is likely

YYMMDD10 not YYMMDDD10. The informat does not want or need the separator provided.

If you want an actual SAS date value then this should work

DATA Test_Out;
   FORMAT tod_datum yymmddd10.;

   INFORMAT tod_datum yymmdd10.;

   INFILE 'C:\Test_In.csv' delimiter=',' MISSOVER firstobs=2 DSD
       LRECL=32767;
INPUT tod_datum .
RUN;

If you want a CHARACTER value then just read the number of characters but the mishmash of code makes it hard to tell which you actually want.