topic Re: Determine if a variable contains a SAS date value in SAS Programming

Determine if a variable contains a SAS date value

Patrick — Thu, 05 Feb 2015 12:17:35 GMT

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.

Re: Determine if a variable contains a SAS date value

Ksharp — Thu, 05 Feb 2015 13:12:01 GMT

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 ?

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 .

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

Re: Determine if a variable contains a SAS date value

RW9 — Thu, 05 Feb 2015 13:14:43 GMT

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.

Re: Determine if a variable contains a SAS date value

data_null__ — Thu, 05 Feb 2015 14:31:12 GMT

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

Re: Determine if a variable contains a SAS date value

Patrick — Thu, 05 Feb 2015 21:41:30 GMT

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

Re: Determine if a variable contains a SAS date value

JohnT — Thu, 05 Feb 2015 22:10:11 GMT

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;

Re: Determine if a variable contains a SAS date value

Patrick — Fri, 06 Feb 2015 03:27:27 GMT

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

Re: Determine if a variable contains a SAS date value

Patrick — Fri, 06 Feb 2015 03:30:11 GMT

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.

Re: Determine if a variable contains a SAS date value

Reeza — Fri, 06 Feb 2015 03:36:14 GMT

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

SAS(R) 9.4 Formats and Informats: Reference

Re: Determine if a variable contains a SAS date value

Ksharp — Fri, 06 Feb 2015 06:06:38 GMT

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

Re: Determine if a variable contains a SAS date value

Ksharp — Fri, 06 Feb 2015 06:09:02 GMT

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

Re: Determine if a variable contains a SAS date value

RW9 — Fri, 06 Feb 2015 09:00:19 GMT

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.

Re: Determine if a variable contains a SAS date value

data_null__ — Fri, 06 Feb 2015 11:06:35 GMT

You don't understand.

Re: Determine if a variable contains a SAS date value

Ksharp — Sat, 07 Feb 2015 05:22:25 GMT

:smileyconfused:

Re: Determine if a variable contains a SAS date value

data_null__ — Sat, 07 Feb 2015 14:32:54 GMT

Yes, you are.

Re: Determine if a variable contains a SAS date value

LongTimer — Thu, 20 Jul 2017 21:05:47 GMT

Test if value is a date-literal, or not.
Use the input-function, with the "??" option, and see if a missing-value is returned or not. Use the "ANY" informats, to sequentially test for valid date and date-time literal-values.
If a missing value is returned, assume the value is a SAS-date or SAS-datetime.
Test if the value is strictly a SAS-date, as follows: if(^('01JAN1960'd<=SasDate<=today()))
Use a similar test, to check for valid date-time values.

Re: Determine if a variable contains a SAS date value

Demoxe — Thu, 19 Oct 2017 07:26:03 GMT

I guess it's too late to answer, but I think I've found an interesting solution.

You can get 1 observation from your initial table with the field in question, then set this field's value to current date and time or something like '31DEC9999:0:0:0'dt and then use function vvalue(field). If this field uses date format without time, then our datetime value will be too big for this format, so vvalue() will return the string, consisting from '*' characters. Otherwise vvalue() will return something, that resembles date and time. Depending on the output we can determine, was the field in question in date format or in datetime format. Code is below:

data _NULL_;

set in_table;

field='31DEC9999:0:0:0'dt;

/* If the string consists only from '*' characters, then compress() below will return empty string */

if compress(vvalue(field),'*')='' then call symput('date_format','date');

else call symput('date_format','datetime');

run;