topic Re: Any way to check datatype of a variable date or datetime ? in SAS Programming

Any way to check datatype of a variable date or datetime ?

anandbillava — Thu, 29 Apr 2010 16:45:50 GMT

Is there any way to check a variable is holding date value or datetime value ?
There are various date and date time formats can be assigned to a variable. So its difficult to know is the variable having date value or date time value.
I am doing some processing in my program depending up on the variable datatype (date or datetime). But variables are having different kinds of formats.

Can any body suggest me some idea ? Message was edited by: anandbillava

Re: Any way to check datatype of a variable date or datetime ?

Cynthia_sas — Thu, 29 Apr 2010 17:28:04 GMT

Hi:
Variables that are SAS date or date/time variables will always be NUMERIC variables....SAS knows that they are meant to be used as DATE or DATE/TIME values by the presence of a format (for display) or an informat (for input).

You might investigate the use of the Dictionary tables or SASHELP files that contain variable information (DICTIONARY.COLUMNS or SASHELP.VCOLUMNS) to determine which variables in which datasets have date or date/time formats/informats assigned.

These papers outline the use of the DICTIONARY and SASHELP files.
http://www2.sas.com/proceedings/sugi29/237-29.pdf
http://www2.sas.com/proceedings/sugi31/259-31.pdf
http://www2.sas.com/proceedings/sugi30/070-30.pdf

cynthia

Re: Any way to check datatype of a variable date or datetime ?

anandbillava — Thu, 29 Apr 2010 18:23:10 GMT

I am using vcolumns table only. But date and datetime format could be anything. Users can create datasets with different date and datetime formats.
If I am not wrong the formats could be any one of the format from below URL

http://www.uc.edu/sashtml/lrcon/zenid-63.htm

So Its difficult to know which formats he is using and thus determining the variable datatype date or datetime. Otherwise we have to check for each of the condition from the above URL to determine is it date or datetime..

Re: Any way to check datatype of a variable date or datetime ?

Cynthia_sas — Thu, 29 Apr 2010 19:36:07 GMT

Hi:
Yes, you're right, you would have to check whether a DATE or DATE/TIME format or informat is being used. That URL shows an older list of formats.

The most current list of formats is here:
http://support.sas.com/documentation/cdl/en/lrdict/62618/HTML/default/a001263753.htm

...and the most current list of informats is here:
http://support.sas.com/documentation/cdl/en/lrdict/62618/HTML/default/a001239776.htm

If you made a dataset of the possible format and/or informat names, you could do an SQL join or a merge to find whether a column name in DICTIONARY.COLUMNS matched one of the formats or informats in your possible formats dataset.

cynthia

Re: Any way to check datatype of a variable date or datetime ?

anandbillava — Thu, 29 Apr 2010 19:40:53 GMT

Hi cynthia,
Thanks. But those formats are valid in SAS 9.1.3 ?
Because I am still using SAS 9.1.3

Anand

Re: Any way to check datatype of a variable date or datetime ?

barheat — Thu, 29 Apr 2010 19:50:57 GMT

You can identify the format being used by running proc contents to a data table and examining the format column.

proc contents data=filename noprint out=table_contents;
run;

Another way is to examine the unformatted values. Dates will be 5 digits. Datetimes will be 10 or 11 digits.

Re: Any way to check datatype of a variable date or datetime ?

anandbillava — Thu, 29 Apr 2010 20:13:24 GMT

I know about that.
But my question to cynthia was the URL he mentioned with new formats are for version 9.1.3 or 9.2 ?

Re: Any way to check datatype of a variable date or datetime ?

Cynthia_sas — Thu, 29 Apr 2010 22:07:08 GMT

Hi:
That original URL with the list of formats looked to me like the old Version 8 form of the documentation. Since it wasn't a support.sas.com URL, it is hard to say. However,

If you need to check the 9.1.3 documentation, then the 9.1.3 list of formats is here:
http://support.sas.com/documentation/onlinedoc/91pdf/sasdoc_913/base_lrdictionary_10307.pdf (page 84) or
http://support.sas.com/onlinedoc/913/getDoc/en/lrdict.hlp/a001263753.htm

The documentation links that I previously posted were from the 9.2 documentation. However, there would be no harm (except a fractional amount of processing) in using the 9.2 list -- because then you would not have to revise the program again for SAS 9.2.

cynthia

Re: Any way to check datatype of a variable date or datetime ?

anandbillava — Fri, 30 Apr 2010 00:05:38 GMT

See you are saying the one you posted in the first URL can work for both the version. or Do i need join both the information ?

Re: Any way to check datatype of a variable date or datetime ?

sbb — Fri, 30 Apr 2010 02:05:20 GMT

Consider using a "reasonable value" check which would involve using a "date" or "datetime" range generated using the INTNX function and you decide what might be a reasonable range to test -- if the value is outside the range then it must not be the type you are testing for. Of course you could also test the equivalent "datetime" range as well. I agree that using a test for particular output formats is problematic, when date-related SAS output formats are numerous. Better to check for "a value within a declared range using INTNX".

Scott Barry
SBBWorks, Inc.

Re: Any way to check datatype of a variable date or datetime ?

data_null__ — Fri, 30 Apr 2010 11:57:32 GMT

If you decide to use the associated format to determine SAS Date/Time related variables it might be easier to let your data help you build the lookup table. You can add to it with each new library. The code below looks at a library and builds a list of unique format names. From this you can make an INFORMAT that would incode a format name as you desire.

[pre]
proc contents noprint
out=fmts(keep=format where=(not missing(format)))
data=sashelp._all_;
run;
proc sort data=fmts nodupkey;
by format;
run;
proc print;
run;
[/pre]

Using the output from PROC CONTENTS create an INFORMAT...

[pre]
data control;
retain fmtname 'ftyp' type 'J' hlo 'UJ ';
infile cards eof=eof;
input label:$2. start:$32.;
output;
return;
eof:
call missing(start);
label = '?';
hlo = cats(hlo,'O');
output;
cards;
C $
C $CHAR
C $HEX
N BEST
N COMMA
D DATE
DT DATETIME
N DOLLAR
N HEX
D MONNAME
D MONYY
T TIME
D WEEKDATX
D YEAR
D YYQ
D YYQC
N Z
;;;;
run;
proc format cntlin=control;
select @$ftyp;
run;
[/pre]

Re: Any way to check datatype of a variable date or datetime ?

Cynthia_sas — Fri, 30 Apr 2010 15:52:21 GMT

Yes, the 9.2 documentation for formats and informations should contain all the 9.1.3 formats and informats PLUS any new formats and informats that were added for 9.2. Generally, formats and informats are ADDED in new releases. I am not aware of any formats or informats that have been removed.

cynthia

Re: Any way to check datatype of a variable date or datetime ?

anandbillava — Sun, 02 May 2010 13:28:20 GMT

But the problem here is user can have format defined as ddmmyy10. Here the 10 number is always floating. So there is goin to be lot of formats with this.

Re: Any way to check datatype of a variable date or datetime ?

Peter_C — Sun, 02 May 2010 16:11:33 GMT

anandbillava

since the possible list of date and datetime formats is very long
(consider that there are many NLS formats for each of the basic date and time formats, and that a user table could use a date format that is user-defined with proc format, I do not think providing a specific list will be a 100% solution),
but, perhaps you do not seek a 100% solution.
If so read on:[pre]1 %put %sysfunc( putn("31dec3000"d , best12 )) ;
380217
2 %put %sysfunc( putn("31dec1990:0:0"dt, best12 )) ;
978220800[/pre] indicates that the largest date I imagine you need to consider(31Dec3000 ?) and the earliest timestamp (beginning of 1990?) are so far apart, you can use the value range to indicate what the value holds.... whether date (value < 380217) or timestamp (value > 400000)
In fact a value 400000 refers to a timestamp at the beginning of the 1960's >>>> [pre]3 %put %sysfunc( putn( 400000, datetime. ));
05JAN60:15:06:40
4 %put %sysfunc( putn( 400000, date11. ));
01-MAR-3055[/pre] and a date in year 3055. I guess that provides enough separation for most data management needs.

unless you have more to explain ...?

hoping to hear that this is the way you are happy "to check datatype of a variable date or datetime"
or that you mave more specific requirements

PeterC

Re: Any way to check datatype of a variable date or datetime ?

Cynthia_sas — Sun, 02 May 2010 18:06:40 GMT

I guess I'm confused by your description of what you want. At first, I thought you wanted to search through a library and find out whether there were any date variables based on looking up the variable format compared to a list. But then some other folks interpreted your question as wanting to know whether the variable VALUES were valid date or date/time values. However, I'm going to stick with my original interpretation...and guess that this most recent question has to do with the fact that the documentation lists just the "base" name of the format (or informat) and doesn't show a width, while in actual usage, the format width can vary.

For example, as you note, a variable could be defined for actual usage with a format of DDMMYY10 or DDMMYY8 or MMDDYY10 or MMDDYY6 or MMDDYY8. So yes, the WIDTH of the formatted field could always be different in the dataset descriptor. However, the basic format name for the above 2 examples is either DDMMYY or MMDDYY....without regard to the WIDTH of the formatted value. You can easily read the FORMAT variable out of DICTIONARY.COLUMNS and extract only the character portion of the format name using the SCAN function. Something like this:
[pre]
method1 = scan(fmtval,1,'1234567890.');
method2 = scan(fmtval,1,,'dp');
[/pre]

The first scan method statement just hard-codes digits and a period as the delimiters for the SCAN function. The second method uses the 'dp' modifiers to the SCAN function instead of typing the list of numbers and punctuation. In either case, the result of using both methods would be that MMDDYY8. and MMDDYY10. as format values would become MMDDYY ... In the program below, I use the first method to compare formats from DICTIONARY.COLUMNS against a small checklist dataset of format names. Partial output from the program is shown after the program.

With this report in hand, you could either code a macro program or code a series of PROC FREQ, PROC MEANS or PROC UNIVARIATE steps to test the values in these particular variables to see what the range of date or date/time values was and whether they were reasonable values for the processing you need to do.

cynthia
[pre]
data checklist;
infile datalines;
input fwant $;
select_match = 'FORMAT';
fwant = upcase(fwant);
return;
datalines;
date
mmddyy
monname
weekdate
datetime
;
run;

ods listing;
options linesize=150;

proc sql;
create table datevars as
select b.select_match, a.libname, a.memname, a.name, a.type,
a.informat, a.format,
scan(a.format,1,'1234567890.') as f_name,
a.length, a.label
from dictionary.columns as a,
work.checklist as b
where (calculated f_name = b.fwant)
order by a.libname, a.memname, a.name;
quit;

proc print data=work.datevars;
title 'Date variables in DICTIONARY.COLUMNS with date format';
run;

[/pre]

Partial output:
[pre]
select_
Obs match libname memname name type informat format f_name length label

1 FORMAT MAPS NCC DATE num DATETIME20. DATETIME 8
2 FORMAT MAPS NCD DATE num DATETIME20. DATETIME 8
3 FORMAT MAPS NCN DATE num DATETIME20. DATETIME 8
4 FORMAT MAPS USAC DATE num DATETIME20. DATETIME 8
5 FORMAT MAPS USAD DATE num DATETIME20. DATETIME 8
6 FORMAT MAPS USAN DATE num DATETIME20. DATETIME 8
7 FORMAT MAPS WAKEC DATE num DATETIME20. DATETIME 8
8 FORMAT MAPS WAKED DATE num DATETIME20. DATETIME 8
9 FORMAT MAPS WAKEN DATE num DATETIME20. DATETIME 8
10 FORMAT SASHELP ASSOCWA _LOADTM num DATETIME20. DATETIME20. DATETIME 8 DateTime Stamp of when row was loaded
11 FORMAT SASHELP BUY DATE num DATE9. DATE 8 Date
12 FORMAT SASHELP CITIDAY DATE num DATE9. DATE 7 Date of Observation
13 FORMAT SASHELP GNGSMP2 Date num DATE. DATE 8
14 FORMAT SASHELP GNGSMP2 DateTime num DATETIME14. DATETIME 8
15 FORMAT SASHELP MDV SHIPDATE num DATE. DATE7. DATE 8
16 FORMAT SASHELP NVST1 DATE num DATE9. DATE 8 Date
17 FORMAT SASHELP NVST2 DATE num DATE9. DATE 8 Date
18 FORMAT SASHELP NVST3 DATE num DATE9. DATE 8 Date
19 FORMAT SASHELP NVST4 DATE num DATE9. DATE 8 Date
20 FORMAT SASHELP NVST5 DATE num DATE9. DATE 8 Date
21 FORMAT SASHELP PRDSAL2 MONTH num MONNAME3. MONNAME 8 Month
22 FORMAT SASHELP PRDSAL3 MONTH num MONNAME3. MONNAME 8 Month
23 FORMAT SASHELP PRDSALE MONTH num MONNAME3. MONNAME 8 Month

[/pre]

Re: Any way to check datatype of a variable date or datetime ?

ChrisNZ — Mon, 03 May 2010 01:59:50 GMT

Another (quick and dirty) way is to test actual values.

3 millions gives you a date past year 9999, but a date time in feb 1960.

so unless you deal with dates going back to 1960, you can be safe assuming that

value > 3e6 means datetime

value < 3e6 means date.

If you might use 1960 or older dates, you need to use formats as seen above.

Re: Any way to check datatype of a variable date or datetime ?

anandbillava — Mon, 03 May 2010 14:45:22 GMT

Thanks cynthia. I used compress method instead of scan function.
I have completed my program on this. Its working fine now.

Re: Any way to check datatype of a variable date or datetime ?

monei011 — Wed, 05 May 2010 03:15:32 GMT

The problem you have is that SAS only has two data types: Character and Numeric. SAS stores dates and times as numeric data type and the fact that the value stored is a date or date time is lost unless you document that somewhere.
Assigning a format or informat is one way to document it (internally within the dataset itself) but that is prone to problems, because anyone who has write access to your dataset can change the format or informat attribute. Not ony that someone might just rename your variable and remove the format and informat.
The dictionary tables can be used to extract a lot of attibute information that can then be used programatically. If you do know the date and datetime ranges reliably (ie the data was entered under some programatic control over the values entered and not just entered as free text) then you can do some basic range testing. you could also use a number of SAS functions for extracting parts of the date or date time, and see if they conformed to the valid range of values.
You can also use the put function to write a value in a specified date or date time format as text, assign the value to a character variable and then use character manipulation functions to pull off parts of the text and compare agains a list of month names for example.

If you have no knowledge or certainty about the source of the data, and that the values of a particular variable are dates or date times, then you expose yourself to some risk in making that assumption. The risk will depend on the consequences of the error.