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

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

knveraraju91 — Tue, 24 Apr 2018 14:01:17 GMT

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;

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

tomrvincent — Tue, 24 Apr 2018 14:24:44 GMT

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;

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

RW9 — Tue, 24 Apr 2018 14:37:50 GMT

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-lengths-are/m-p/456523#M115643

(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.

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

Shmuel — Tue, 24 Apr 2018 14:52:41 GMT

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;

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

tomrvincent — Tue, 24 Apr 2018 17:45:10 GMT

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. 🙂

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

Shmuel — Tue, 24 Apr 2018 18:20:17 GMT

@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.

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

RW9 — Wed, 25 Apr 2018 08:20:33 GMT

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.