BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
swimmer
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
data_null__
Jade | Level 19

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

11 REPLIES 11
ballardw
Super User

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;

Tom
Super User Tom
Super User

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

art297
Opal | Level 21

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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. 

data_null__
Jade | Level 19

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
PaigeMiller
Diamond | Level 26

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

--
Paige Miller
Haikuo
Onyx | Level 15

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

art297
Opal | Level 21

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?

ChrisNZ
Tourmaline | Level 20

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.

ChrisNZ
Tourmaline | Level 20

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

swimmer
Calcite | Level 5

Thank you all! You guys are amazing!

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
  • 11 replies
  • 8762 views
  • 11 likes
  • 9 in conversation