turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- Numeric date variable with some values dates and s...

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-18-2018 05:13 AM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ColmSmyth

04-18-2018 05:22 AM

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.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Astounding

04-18-2018 05:32 AM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ColmSmyth

04-18-2018 05:26 AM

@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;
```

---------------------------------------------------------------------------------------------

Maxims of Maximally Efficient SAS Programmers

How to convert datasets to data steps

How to post code

Maxims of Maximally Efficient SAS Programmers

How to convert datasets to data steps

How to post code

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to KurtBremser

04-18-2018 05:31 AM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ColmSmyth

04-18-2018 05:39 AM

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

---------------------------------------------------------------------------------------------

Maxims of Maximally Efficient SAS Programmers

How to convert datasets to data steps

How to post code

Maxims of Maximally Efficient SAS Programmers

How to convert datasets to data steps

How to post code

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to KurtBremser

04-18-2018 05:43 AM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ColmSmyth

04-18-2018 05:47 AM

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

---------------------------------------------------------------------------------------------

Maxims of Maximally Efficient SAS Programmers

How to convert datasets to data steps

How to post code

Maxims of Maximally Efficient SAS Programmers

How to convert datasets to data steps

How to post code

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ColmSmyth

04-18-2018 05:48 AM

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.