how to check if the var is date or datetime

Accepted Solution Solved
Reply
Contributor
Posts: 36
Accepted Solution

how to check if the var is date or datetime

Hi,

I need to write a macro working on two datasets. They both have variable 'date1', but one if them is real date (eg 2015/01/01) and the other one is datetime (eg 01Jan2015:00:00:00). I want to extract the date part of date1. It is something like

If it is datetime, then I need to run   ---- date2 = datapart(date1);

if it is real date, then I need to run   ----- date2 = date1;

How can I write the condition part of the statement?

Thanks.


Accepted Solutions
Solution
‎02-19-2015 07:26 AM
Respected Advisor
Posts: 3,775

Re: how to check if the var is date or datetime

I see those date time variables all the time.  They may have actual time-part or often the time is 00:00;00 but they always have the proper format associated and that can be exploited by ANYDTDTE informat.

data date(keep=date1 rename=date1=date) datetime(keep=date2 rename=date2=date);
   input date1 date9. @1 date2 datetime22.3;
  
format date1 mmddyyd10. date2 datetime.;
  
cards;
10JAN2013:00:00:00.000
22JAN2013:00:00:00.000
18DEC2012:00:00:00.000
30OCT2012:00:00:00.000
29NOV2012:00:00:00.000
17APR2014:11:37:45.157
17APR2014:13:28:45.887
17APR2014:11:37:45.157
25APR2014:18:41:42.090
17APR2014:13:28:45.887
;;;;
   run;
proc print data=date;
proc print data=datetime;
   run;
%macro main(data=);
   data new;
      length dsname $
41;
      set &data indsname=indsname;
      dsname = indsname;
      new = input(vvalue(date),
anydtdte22.);
      format new date9.;
      run;
   proc print;
      run;
  
%mend main;
%main(data=date);
%main(data=datetime);

2-19-2015 6-22-38 AM.png

View solution in original post


All Replies
Grand Advisor
Posts: 10,218

Re: how to check if the var is date or datetime

Are you working on the data sets separately or is this after/during a merge? If the first I don't see why it is conditional. If you are combining datasets either with merge or set statements then us the IN dataset option:

data want;

     set have1 (in=set1) have2 (in=set2);

     if set1 then <code>;

     if set2 then <other code>;

run;

Yes could use Else but if you end up combining more sets then ELSE may not be appropriate;

Super User
Super User
Posts: 6,364

Re: how to check if the var is date or datetime

It works much better if you KNOW which variable is using DATE and which is using DATETIME.

How about looking at the attached format?

Just looking at a single value might not work unless you know specifically what types of dates you expect.  But if you are looking a Birth Dates for example then you could have some valid datetime values that fall into the range normally associated with DATE values.  You might have some luck looking at the range of values.

data check ;

  do year=-85 to 0 ;

    date=intnx('year',today(),year);

    datetime=dhms(date,0,0,0);

    output;

  end;

run;

proc summary data=check nway ;

  var date datetime;

  output out=ranges range= min= max= /autoname;

run;

proc print; run;

                           date_     datetime_                 datetime_                 datetime_

Obs    _TYPE_    _FREQ_    Range       Range      date_Min        Min       date_Max        Max

1        0        86      31046    2682374400     -10957     -946684800      20089     1735689600

Esteemed Advisor
Posts: 7,295

Re: how to check if the var is date or datetime

Tom: The only range of datetimes that could be a problem, I think, would be Jan 1, 1960 between midnight and 6am.

If the OP is certain that such a value can't exist in their data, then

if abs(datetime) gt 21600

would work for all dates through 2019

Esteemed Advisor
Esteemed Advisor
Posts: 7,232

Re: how to check if the var is date or datetime

I would agree with Tom here.  How are you dealing with data if you don't know what format the data is in?  Know your underlying data and the coding becomes far easier, i.e. you wouldn't need a macro or complicated branching code. 

Solution
‎02-19-2015 07:26 AM
Respected Advisor
Posts: 3,775

Re: how to check if the var is date or datetime

I see those date time variables all the time.  They may have actual time-part or often the time is 00:00;00 but they always have the proper format associated and that can be exploited by ANYDTDTE informat.

data date(keep=date1 rename=date1=date) datetime(keep=date2 rename=date2=date);
   input date1 date9. @1 date2 datetime22.3;
  
format date1 mmddyyd10. date2 datetime.;
  
cards;
10JAN2013:00:00:00.000
22JAN2013:00:00:00.000
18DEC2012:00:00:00.000
30OCT2012:00:00:00.000
29NOV2012:00:00:00.000
17APR2014:11:37:45.157
17APR2014:13:28:45.887
17APR2014:11:37:45.157
25APR2014:18:41:42.090
17APR2014:13:28:45.887
;;;;
   run;
proc print data=date;
proc print data=datetime;
   run;
%macro main(data=);
   data new;
      length dsname $
41;
      set &data indsname=indsname;
      dsname = indsname;
      new = input(vvalue(date),
anydtdte22.);
      format new date9.;
      run;
   proc print;
      run;
  
%mend main;
%main(data=date);
%main(data=datetime);

2-19-2015 6-22-38 AM.png
Trusted Advisor
Posts: 1,499

Re: how to check if the var is date or datetime

Wow, that is a very impressive solution (as usual)! I'm going to have to steal this.

Respected Advisor
Posts: 3,124

Re: how to check if the var is date or datetime

I second @PaigeMiller, first time saw VVALUE alive! It is fortunate that knowledge can sustain unlimited stealing Smiley Happy.

Esteemed Advisor
Posts: 7,295

Re: how to check if the var is date or datetime

If date and datetime variables always have a correct format associated with them, couldn't the OP simply take advantage of exploiting the vvalue function? e.g., simply using something like 'if length(vvalue(date1)) gt 10' to identify datetime values?

Trusted Advisor
Posts: 1,510

Re: how to check if the var is date or datetime

If you want to use the formats, function vformat() would be the best way to do it. Something like (this is incomplete) :

data T1;
  V=today();     output;
  V=datetime();  output;
  format V datetime.;
data T2;
  set T1;
  F=vformat(V);
  X= ifn( F =: 'DATETIME' or find(F,'DT')      , datepart(V)
    ,ifn( F in: ('YYMM','DDMM','MMDD','DATE')  , V
    ,ifn( V < 25000                            , V
    ,                                            datepart(V) )));
  putlog X X date.;
run
;

but using the formatted value directly and leveraging the anydtdte. informat as shown by data _null_ is the most elegant way.

Trusted Advisor
Posts: 1,510

Re: how to check if the var is date or datetime

Swimmer, don't forget to acknowledge the helpful answer(s).

Contributor
Posts: 36

Re: how to check if the var is date or datetime

Thank you all! You guys are amazing!

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 11 replies
  • 1289 views
  • 10 likes
  • 9 in conversation