DATA Step, Macro, Functions and more

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

Reply
Contributor
Posts: 72

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

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
SAS Super FREQ
Posts: 8,866

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

Posted in reply to anandbillava
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
Contributor
Posts: 72

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

Posted in reply to Cynthia_sas
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..
SAS Super FREQ
Posts: 8,866

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

Posted in reply to anandbillava
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
Contributor
Posts: 72

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

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

Anand
Contributor
Posts: 36

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

Posted in reply to anandbillava
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.
Contributor
Posts: 72

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

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 ?
SAS Super FREQ
Posts: 8,866

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

Posted in reply to anandbillava
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
Contributor
Posts: 72

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

Posted in reply to Cynthia_sas
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 ?
Super Contributor
Super Contributor
Posts: 3,174

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

Posted in reply to anandbillava
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.
Respected Advisor
Posts: 3,799

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

Posted in reply to anandbillava
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]
SAS Super FREQ
Posts: 8,866

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

Posted in reply to anandbillava
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
Contributor
Posts: 72

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

Posted in reply to Cynthia_sas
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.
Valued Guide
Posts: 2,177

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

Posted in reply to anandbillava
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
SAS Super FREQ
Posts: 8,866

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

Posted in reply to anandbillava
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]
Ask a Question
Discussion stats
  • 17 replies
  • 13406 views
  • 2 likes
  • 8 in conversation