DATA Step, Macro, Functions and more

How to populate date and datetime values under one variable without imputing time when time missing

Reply
Super Contributor
Posts: 312

How to populate date and datetime values under one variable without imputing time when time missing

Dear,

 

Some one from support helped me in the similar issue but that code is not getting the output i need in this issue. As specs changed so I have to update it.

I need to derive a numeric variable 'b' without imputing time when time is missing. I tried a few ways but did not get what i need.

Please sugest. Thank you

ouput needed;

01SEP17:15:10:00

04SEP17

 

data one;
  input a $16.;
datalines;
2017-09-01T15:10
2017-09-04
;
run; 

 

Regular Contributor
Posts: 181

Re: How to populate date and datetime values under one variable without imputing time when time miss

Posted in reply to knveraraju91

Convert to an appropriate format depending on the length of a. 

 

Here's an example.

 

data one;
  format a datetime20.; input a E8601DT20.;
datalines;
2017-09-01T15:10
2017-09-04
;
run;

data two;
  format b datetime20.; input b E8601DN. ;
datalines;
2017-09-01T15:10
2017-09-04
;
run;

Trusted Advisor
Posts: 1,831

Re: How to populate date and datetime values under one variable without imputing time when time miss

Posted in reply to tomrvincent

Thanks to @tomrvincent I have learned also new informats for me.

 

It can be done in the same step in any of the next two examples:

data one;
  input a $16.;
  if length(a) = 10 
then my_dt = input(substr(a,1,10), E8601DN10.); else my_dt = input(a, E8601DT16.); datalines; 2017-09-01T15:10 2017-09-04 ; run;

or alternatively by:

data one;
  input a $16.;
  if substr(a,11,1) = 'T' 
then my_dt = input(substr(a,1,10), E8601DN10.); else my_dt = input(a, E8601DT16.); datalines; 2017-09-01T15:10 2017-09-04 ; run;

 

 

Regular Contributor
Posts: 181

Re: How to populate date and datetime values under one variable without imputing time when time miss

Glad I could help.  Please feel free to mark my post as a solution so that I get an extra mug of mead in SAS Valhalla. Smiley Happy

Super User
Super User
Posts: 9,441

Re: How to populate date and datetime values under one variable without imputing time when time miss

Posted in reply to knveraraju91

As I said in the previous post on this question:

https://communities.sas.com/t5/Base-SAS-Programming/Date-time-informat-that-works-when-the-date-time...

(Which you failed to get back to me on)

 

It is quite simple.  Dates, times, datetimes are numbers, dates are number of days since cutoff, time is number of seconds since midnight, datetime is number of seconds since cuttoff.  So if you want a number that number needs to reflect the data which is stored, either date, time, or datetime.  You cannot store the number for date, then the number for datetime in one variable, this is not possible.  Either the variable contains date/time numbers or it contains date numbers.  This is also why, when applying a format, that format applies to every row in the variable, so if you say display as datetime, then all values will either be datetime or missing.  

 

So, from that you can only have one or the other in terms of a numeric variable.

You could of course switch it over an use a character variable, much like you have in your given example.  As that is text - and does not reflect any internal storage - it can contain anything you like, hence why ISO dates are text.  

 

Once again, numeric dates, times, datetimes need to contain all parts to be valid numeric variables.

Trusted Advisor
Posts: 1,831

Re: How to populate date and datetime values under one variable without imputing time when time miss

@RW9, according to documentation I have, having a date in a form of yyyy-mm-dd 

using informat  E8601DN10. will assume time 00:00:00 and create a datetime variable.

Please check me.

 

Super User
Super User
Posts: 9,441

Re: How to populate date and datetime values under one variable without imputing time when time miss

Hi @Shmuel.  You are indeed correct. When reading the data using this informat it does impute the missing 00:00:00.  However I thought from the posters original post that they wanted to display the datetime information after reading as two different date formats, which is a different matter.

Ask a Question
Discussion stats
  • 6 replies
  • 113 views
  • 4 likes
  • 4 in conversation