DATA Step, Macro, Functions and more

converting character date to date9.

Accepted Solution Solved
Reply
Regular Contributor
Posts: 160
Accepted Solution

converting character date to date9.

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.


Accepted Solutions
Solution
‎05-22-2017 08:33 AM
Super User
Super User
Posts: 7,942

Re: converting character date to date9.

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;

View solution in original post


All Replies
Super User
Posts: 7,762

Re: converting character date to date9.

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
PROC Star
Posts: 734

Re: converting character date to date9.

[ Edited ]

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;
Regular Contributor
Posts: 160

Re: converting character date to date9.

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"

PROC Star
Posts: 734

Re: converting character date to date9.

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

Regular Contributor
Posts: 160

Re: converting character date to date9.

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

Super User
Posts: 5,498

Re: converting character date to date9.

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?

Solution
‎05-22-2017 08:33 AM
Super User
Super User
Posts: 7,942

Re: converting character date to date9.

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;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 325 views
  • 1 like
  • 5 in conversation