topic Re: converting character date to date9. in SAS Programming

converting character date to date9.

vraj1 — Mon, 22 May 2017 08:45:37 GMT

I have a date variable which is character with length as 50. and i want to convert it to date9.

sample of date:

AESTDTC

2017-03-14T08:00

2016-12-16

2017-04-10T20:00

i am using the below code:

data nkd;

set adae;

format AESTDT date9.;
AESTDT = input( AESTDTC, MMDDYY10.);
run;

i get error as below. can anyone help me out in this

NOTE: Invalid argument to function INPUT at line 51 column 241.

Re: converting character date to date9.

Kurt_Bremser — Mon, 22 May 2017 08:54:18 GMT

Do this:

data nkd;
set adae;
format AESTDT date9.;
AESTDT = input(substr(AESTDTC,1,10),yymmdd10.);
run;

Although I'd rather expand incomplete timestamps with a default time value and use the e8601dt informat, so that the timestamps are preserved.

Re: converting character date to date9.

PeterClemmensen — Mon, 22 May 2017 08:55:42 GMT

like this?

data have;
input AESTDTC $50.;
datalines;
2017-03-14T08:00
2016-12-16
2017-04-10T20:00
;

data want;
	set have;
	date = input(substr(AESTDTC,1,10), yymmdd10.);
	format date date9.;
run;

Re: converting character date to date9.

vraj1 — Mon, 22 May 2017 09:06:43 GMT

Thanks,

Sorry for this again. I now can see i have data which has missing or partial and it causes error "2016-06" missing day.

In proc report i am mentioning format=datetime7.

and i get all date as 01jan60(for all obs)

desired format is "06SEP13"

Re: converting character date to date9.

PeterClemmensen — Mon, 22 May 2017 09:08:41 GMT

Not sure I understand this, so you have some datapoints where the day of the date is missing?

Re: converting character date to date9.

vraj1 — Mon, 22 May 2017 09:17:49 GMT

sorry for not being clear.

Now i made the dates in numeric format date9. as example 02JAN2017

and in proc report for the variable i am using format=date7.; as i need the final date in "06SEP13" but its not working

Re: converting character date to date9.

RW9 — Mon, 22 May 2017 09:21:10 GMT

Please post test data in the form of a datastep, I think this has been mentioned several times now.

First point with any data is to get it into a useable format. What you have there are ISO dates, pretty standard in the SDTM you are displaying there. Now the first question would be what are you doing to want this data, genrally speaking you would goto ADaM first where you can have both the text for display and numerics for calculation.

You can however process the data, first, I would have something in the documentation which handles missing or partial dates/times - this is far more important than any coding requirements. Then its simply a matter of implementing these rules. For instance in the below I have assumed 00:00 if time is missing, and all will have 00 for miliseconds as this is not present in the data. Once I apply this rule then handle dates/times formatting becomes the simplest of tasks:

data have;
  length aestdtc $16;
  input aestdtc $;
  aestdtc=cats(aestdtc,"T00:00");
  aestdtc_n=input(cats(aestdtc,":00"),e8601dt.);
  aestdtc_mmddyy=datepart(aestdtc_n);
  format aestdtc_n datetime. aestdtc_mmddyy mmddyy10.;

datalines;
2017-03-14T08:00
2016-12-16
2017-04-10T20:00
;
run;

Re: converting character date to date9.

Astounding — Mon, 22 May 2017 10:26:26 GMT

A couple of items are concerning.

First, if you have a variable that takes on date values, why would you try to apply a DATETIME format? That's what gives you 01JAN60. You can use the DATE9 format or the DATE7 format, but not a DATETIME format. Your variable simply isn't storing any time-related information and a pure date should use a date format.

Second, why change the format at all? You already assigned a DATE9 format to your variable. What advantage could you possibly get in your report by shifting to two-digit years?

Finally, it doesn't look like you are telling the full story. What code did you run, so that a missing month should default to SEP?