DATA Step, Macro, Functions and more

Determine if a variable contains a SAS date value

Accepted Solution Solved
Reply
Respected Advisor
Posts: 3,896
Accepted Solution

Determine if a variable contains a SAS date value

Hi all

I need to implement a process which is able to determine if a numeric SAS variable contains a date, datetime or time value based on the format assigned to this variable.

So far I can only think of creating a list of known "out-of-the-box" SAS formats and then compare this list with the format assigned to the SAS variable. But that's not future proof and later versions of SAS could have additional formats.

Is there a more direct way to achieve what I need? Let's say some "IsADate()" function?

If this is not possible and I have to go for the list approach: Does anyone know if and where there would be a location in the SAS docu with ALL date, time & datetime formats (also all the national ones like eurdfdd.)

Thanks

Patrick

8 February 2015

I will now close this thread that it doesn't get too messy. I've posted a follow-up question here

I was hoping for some hidden gem but it appears there is none and I have to do the hard work.


Accepted Solutions
Solution
‎02-05-2015 09:31 AM
Respected Advisor
Posts: 3,777

Re: Determine if a variable contains a SAS date value

Dictionary.formats should contain the list of all format that your SAS has access to.  Unfortunately I don't see how to figure which are date formats from the variables.  Perhaps you can build a table by mining the list.  Something this might get you started.

36        
37         data _null_;
38            set sashelp.vformat(keep=fmtname fmttype source minw mind maxw maxd defw defd );
39            where fmttype eq 'F' and fmtname ne: '$';
40            length value $64;
41            value = putn(0,fmtname);
42            put 'NOTE: ' (fmtname value)(=);
43            run;

NOTE: fmtname=AFRDFDD value=
01.01.60
NOTE: fmtname=AFRDFDE value=
01Jan60
NOTE: fmtname=AFRDFDN value=
5
NOTE: fmtname=AFRDFDT value=
01Jan60:00:00:00
NOTE: fmtname=AFRDFDWN value=Vrydag
NOTE: fmtname=AFRDFMN value=Januarie
NOTE: fmtname=AFRDFMY value=Jan60
NOTE: fmtname=AFRDFWDX value=
1 Januarie 1960
NOTE: fmtname=AFRDFWKX value=Vrydag,
1 Januarie 1960
NOTE: fmtname=B8601DA value=
19600101
NOTE: fmtname=B8601DN value=
19600101
NOTE: fmtname=B8601DT value=
19600101T000000
NOTE: fmtname=B8601DZ value=
19600101T000000+0000
NOTE: fmtname=B8601LZ value=
000000-0800
NOTE: fmtname=B8601TM value=
000000
NOTE: fmtname=B8601TZ value=
000000+0000
NOTE: fmtname=BEST value=
0
NOTE: fmtname=BESTD value=
0
NOTE: fmtname=BESTX value=
0

View solution in original post


All Replies
Super User
Posts: 9,682

Re: Determine if a variable contains a SAS date value

Patrick Matter ,

Is there a more direct way to achieve what I need? Let's say some "IsADate()" function?

No. There is not such function in SAS ,but you can ask SAS to create a one , You know where to require ? Smiley Wink

SAS unlike Oracle DB2 , they all have their own date type variable,Like :

create table have

date   datetime12.

So you can't judge if the integer variable is a date or not .

If this is not possible and I have to go for the list approach: Does anyone know if and where there would be a location in the SAS docu with ALL date, time & datetime formats (also all the national ones like eurdfdd.)

I think you know where to find them , since you are already out there .

For SAS9.2 , you can find them at here:

Language Reference Dictionary --  lrdict.pdf

National Language Support -- nlsref.pdf

Good Luck.

Ksharp

Message was edited by: xia keshan

Super User
Super User
Posts: 7,407

Re: Determine if a variable contains a SAS date value

Sorry, maybe I am missing the point, but if you query SASHELP.VCOLUMN, that will tell you if the format is a date/datetime no?  Or are you trying to avoid typing the few date/datetime combinations in as a list, in which case check for those numeric variables which have a non-numeric in their format column.

Respected Advisor
Posts: 3,896

Re: Determine if a variable contains a SAS date value

Querying SASHELP.VCOLUMN will give me the format name but not tell me if this is a date related format or not.

Super User
Super User
Posts: 7,407

Re: Determine if a variable contains a SAS date value

So my question back would be - how will you tell if the format applied to the column is date?  As the format name is just text, e.g. date9,e8601dt what logical assignment can you give to identify the two in the previous example as dates?  The reason behind this is that dates don't really exist, they are just numbers, so the column is a numeric.  It may have a "picture" associated with a display, however there is no standard naming convention which would make it logical to pull out those formats.  In other languages there are coding standards such as small o before and Object and such like, but formats can be anything.  The user could also define his own formats, say notdate9. which could also be a date, and $10 could also be a date "2014-01-01".  I would suggest you need some more information about the data to evaluate.

Solution
‎02-05-2015 09:31 AM
Respected Advisor
Posts: 3,777

Re: Determine if a variable contains a SAS date value

Dictionary.formats should contain the list of all format that your SAS has access to.  Unfortunately I don't see how to figure which are date formats from the variables.  Perhaps you can build a table by mining the list.  Something this might get you started.

36        
37         data _null_;
38            set sashelp.vformat(keep=fmtname fmttype source minw mind maxw maxd defw defd );
39            where fmttype eq 'F' and fmtname ne: '$';
40            length value $64;
41            value = putn(0,fmtname);
42            put 'NOTE: ' (fmtname value)(=);
43            run;

NOTE: fmtname=AFRDFDD value=
01.01.60
NOTE: fmtname=AFRDFDE value=
01Jan60
NOTE: fmtname=AFRDFDN value=
5
NOTE: fmtname=AFRDFDT value=
01Jan60:00:00:00
NOTE: fmtname=AFRDFDWN value=Vrydag
NOTE: fmtname=AFRDFMN value=Januarie
NOTE: fmtname=AFRDFMY value=Jan60
NOTE: fmtname=AFRDFWDX value=
1 Januarie 1960
NOTE: fmtname=AFRDFWKX value=Vrydag,
1 Januarie 1960
NOTE: fmtname=B8601DA value=
19600101
NOTE: fmtname=B8601DN value=
19600101
NOTE: fmtname=B8601DT value=
19600101T000000
NOTE: fmtname=B8601DZ value=
19600101T000000+0000
NOTE: fmtname=B8601LZ value=
000000-0800
NOTE: fmtname=B8601TM value=
000000
NOTE: fmtname=B8601TZ value=
000000+0000
NOTE: fmtname=BEST value=
0
NOTE: fmtname=BESTD value=
0
NOTE: fmtname=BESTX value=
0
Respected Advisor
Posts: 3,896

Re: Determine if a variable contains a SAS date value

Thanks data_null_. That's a better starting point than trying to collect the formats from the docu.

Super User
Posts: 17,868

Re: Determine if a variable contains a SAS date value

Here's the list of Date/Time by Category from SAS 9.4. AFAIK every SAS version has a version of this. There's also the ISO 8601 list.

But you can apply a date/time format to a variable that's not a date/time Smiley Sad

SAS(R) 9.4 Formats and Informats: Reference

Super User
Posts: 9,682

Re: Determine if a variable contains a SAS date value

John,

But you can't include all the date format like National Date Formate (Patrick has pointed it out) .i.e.  nldate.  nldatetime.

They are all depend on the encoding of SAS session which is EN or CN...... other from different countries.

It is really hard for SAS . So I suggest sponsoring a VOTE for creating such function isDate() isDateTime() .

Xia Keshan

Respected Advisor
Posts: 3,777

Re: Determine if a variable contains a SAS date value

You don't understand.

Super User
Posts: 9,682

Re: Determine if a variable contains a SAS date value

:smileyconfused:

Respected Advisor
Posts: 3,777

Re: Determine if a variable contains a SAS date value

Yes, you are.

Frequent Contributor
Posts: 75

Re: Determine if a variable contains a SAS date value

You could try sticking the value into the anydtdte format and see if it returns a non-null value:

data dates;
   dte = '6feb2015';

  
if input(compress(dte), anydtdte.) ^= .;
run;
Respected Advisor
Posts: 3,896

Re: Determine if a variable contains a SAS date value

That's not the problem I need to solve. I'm having a data set with variables of which I try to figure out which ones have a date format assigned - and this must work for any source data set as I want to implement this as a re-usable bit of code.

Super User
Posts: 9,682

Re: Determine if a variable contains a SAS date value

No. That couldn't include all the scenario either . Like

31012015   01312015 ..........

They are varying at different time . and more important thing is if you create a customize date format via proc format + picture ,

it is more hard to judge if it is a date variable.

Xia Keshan

☑ This topic is SOLVED.

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

Discussion stats
  • 15 replies
  • 3576 views
  • 0 likes
  • 7 in conversation