topic Re: how to check if the var is date or datetime in SAS Programming

how to check if the var is date or datetime

swimmer — Thu, 19 Feb 2015 00:09:51 GMT

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.

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

ballardw — Thu, 19 Feb 2015 00:17:38 GMT

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;

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

Tom — Thu, 19 Feb 2015 03:57:48 GMT

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

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

art297 — Thu, 19 Feb 2015 05:24:07 GMT

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

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

RW9 — Thu, 19 Feb 2015 09:06:04 GMT

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.

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

data_null__ — Thu, 19 Feb 2015 12:26:42 GMT

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);

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

PaigeMiller — Thu, 19 Feb 2015 13:52:30 GMT

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

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

Haikuo — Thu, 19 Feb 2015 14:37:14 GMT

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

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

art297 — Thu, 19 Feb 2015 17:58:36 GMT

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?

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

ChrisNZ — Thu, 19 Feb 2015 20:59:54 GMT

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.

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

ChrisNZ — Thu, 26 Feb 2015 09:25:00 GMT

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

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

swimmer — Thu, 26 Feb 2015 17:33:17 GMT

Thank you all! You guys are amazing!