BookmarkSubscribeRSS Feed
ColmSmyth
Fluorite | Level 6

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

8 REPLIES 8
Astounding
PROC Star

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.

ColmSmyth
Fluorite | Level 6

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 

Kurt_Bremser
Super User

@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;
ColmSmyth
Fluorite | Level 6

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

Kurt_Bremser
Super User

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

ColmSmyth
Fluorite | Level 6

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

Astounding
PROC Star

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 1690 views
  • 4 likes
  • 3 in conversation