topic Re: Numeric date variable with some values dates and some datetime in SAS Programming

Numeric date variable with some values dates and some datetime

ColmSmyth — Wed, 18 Apr 2018 09:13:09 GMT

Hi all

I am fairly sure this is not possible but I want to know if I can have one numeric date variable that has a mixed values some of just dates and other with date and times.

For example, I have VARIABLEX which is character with values such as 2017-09-27 (1822089600) and 2017-10-12T08:35 (21093) where the number in the brackets is the numeric values of each date.

Can I make a new numeric variable which these values formatted ?

Thanks

Colm

Re: Numeric date variable with some values dates and some datetime

Astounding — Wed, 18 Apr 2018 09:22:10 GMT

Sure. First trick is to get the numeric values. Assuming that the strings you presented actually contain the values in parentheses:

data want;

set have;

num_date_mix = input( scan(variablex, 2, '()'), 11.);

format num_date_mix date_mix.;

run;

The trick is to create the format DATE_MIX. properly, so that it accurately displays both dates and times. You can do that, assuming that you have reasonably current values (not datetimes going back to 1960, not dates in the next century). For example, here's the idea (although you may need to fix the syntax):

proc format;

value date_mix low-40000 = [yymmdd10.] 40001-high=[datetime23.];

run;

Of course the format needs to exist in order to add the FORMAT in the DATA step.

Re: Numeric date variable with some values dates and some datetime

Kurt_Bremser — Wed, 18 Apr 2018 09:26:38 GMT

@ColmSmyth wrote:

Hi all

I am fairly sure this is not possible but I want to know if I can have one numeric date variable that has a mixed values some of just dates and other with date and times.

For example, I have VARIABLEX which is character with values such as 2017-09-27 (1822089600) and 2017-10-12T08:35 (21093) where the number in the brackets is the numeric values of each date.

Can I make a new numeric variable which these values formatted ?

Thanks

Colm

I guess you mixed up the numerical values in your example.

The answer to the question is no. A variable in a dataset has the same format throughout.

In your case, I'd convert selectively depending on length, and store the result in separate date and time variables, where the time variable is set to zero or missing when only a date is present in the input.

Example:

data want;
input variablex :$16.;
format mydate yymmddd10. mytime time8.;
if length(variablex) = 10
then do;
  mydate = input(variablex,yymmdd10.);
  mytime = 0;
end;
else do;
  mydate = datepart(input(variablex,e8601dt16.));
  mytime = timepart(input(variablex,e8601dt16.));
end;
cards;
2017-10-12T08:35
2017-09-27
;
run;

Re: Numeric date variable with some values dates and some datetime

ColmSmyth — Wed, 18 Apr 2018 09:31:31 GMT

Hi Kurt

Yes, I did mix those up but yes as expected I did not think this is possible. Ultimately here my goal is to try and compare dates where one may have a time and another may not. If i split per your suggestion and then remake as a datetime value for example SAS will have 00:00:00 etc into it which gets considered in any equation as midnight.

I have other ways around this though but thank you for your suggestion. It would be great however if SAS came up with a format for a date that could understand dates and datetimes in one field

Thanks

Colm

Re: Numeric date variable with some values dates and some datetime

ColmSmyth — Wed, 18 Apr 2018 09:32:53 GMT

Hi there

Thanks for your reply, I was only representing the number date in brackets etc, but think I mixed them up anyway. Not to worry though as per my reply below to Kurt what I want is not possible.

Thanks

colm

Re: Numeric date variable with some values dates and some datetime

Kurt_Bremser — Wed, 18 Apr 2018 09:39:24 GMT

@ColmSmyth wrote:

Hi Kurt

Yes, I did mix those up but yes as expected I did not think this is possible. Ultimately here my goal is to try and compare dates where one may have a time and another may not. If i split per your suggestion and then remake as a datetime value for example SAS will have 00:00:00 etc into it which gets considered in any equation as midnight.

I have other ways around this though but thank you for your suggestion. It would be great however if SAS came up with a format for a date that could understand dates and datetimes in one field

Thanks

Colm

Such variables would not be useful anyway, as you would have to check their contents everytime before any following analysis (like @Astounding's format does), making this impractical.

Re: Numeric date variable with some values dates and some datetime

ColmSmyth — Wed, 18 Apr 2018 09:43:14 GMT

Agreed.....oddly I have tried your code and the e8601dt16. format is saying not valid yet I see it in the list of formats for SAS EG...VARIABLEX is character so the input should work

Re: Numeric date variable with some values dates and some datetime

Kurt_Bremser — Wed, 18 Apr 2018 09:47:03 GMT

Please post that particular log. Which SAS version are you using from EG?

Re: Numeric date variable with some values dates and some datetime

Astounding — Wed, 18 Apr 2018 09:48:11 GMT

If all you need is to detect which it is, you don't need a format for that. Just set up a rule as to high large a date can be. Assuming you have already converted the character values to a mix of dates and datetimes:

if num_value > 40000 then type='datetime';

else type='date';

Better yet (and safer because these sorts of rules have exceptions), create the variable TYPE based on the original VARIABLEX, and save it as part of the data set. That doesn't give you a format, but you don't need it when you have VARIABLEX. It just gives you a clue as to what to do when calculating based on the numeric value.