DATA Step, Macro, Functions and more

to find if a nemeric variable is a date?

Accepted Solution Solved
Reply
Contributor
Posts: 65
Accepted Solution

to find if a nemeric variable is a date?


Hi,

I need to find if a newmeric variable is a date, to replicate a table using connect to oracle.

Kindly help in figuring the functrion or procedure how to do that.,

Regards,

Moovendhan


Accepted Solutions
Solution
‎11-10-2014 03:41 AM
Super User
Posts: 3,261

Re: to find if a nemeric variable is a date?

Posted in reply to DMoovendhan

If you are reading data from Oracle this link gives you the default translations SAS applies to Oracle data types:

SAS/ACCESS(R) 9.4 for Relational Databases: Reference, Fifth Edition

If SAS reads an Oracle datetime column it becomes a numeric column in SAS with the DATETIME format applied, by default.

As there is no data type of DATE or DATETIME in SAS the only reliable way to tell if it is a date is if it has a date-related format or informat assigned to it, or you know the history of where the column came from, for example an Oracle data type of datetime.

Without this information you cannot reliably identify a SAS numeric as a date.

View solution in original post


All Replies
Super User
Posts: 7,866

Re: to find if a nemeric variable is a date?

Posted in reply to DMoovendhan

Is it a SAS table, or a table in Oracle you access via SAS/ACCESS to Oracle?

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Solution
‎11-10-2014 03:41 AM
Super User
Posts: 3,261

Re: to find if a nemeric variable is a date?

Posted in reply to DMoovendhan

If you are reading data from Oracle this link gives you the default translations SAS applies to Oracle data types:

SAS/ACCESS(R) 9.4 for Relational Databases: Reference, Fifth Edition

If SAS reads an Oracle datetime column it becomes a numeric column in SAS with the DATETIME format applied, by default.

As there is no data type of DATE or DATETIME in SAS the only reliable way to tell if it is a date is if it has a date-related format or informat assigned to it, or you know the history of where the column came from, for example an Oracle data type of datetime.

Without this information you cannot reliably identify a SAS numeric as a date.

Super User
Super User
Posts: 7,997

Re: to find if a nemeric variable is a date?

Posted in reply to DMoovendhan

I would recommend learning what your data is before attempting to work with it.  Understanding your data, where it comes from, what it is, and what I represents *before* starting to code will help you far more than trying to shoehorn data into pre-existing code.

Super User
Posts: 10,046

Re: to find if a nemeric variable is a date?

Posted in reply to DMoovendhan

If the table is from Oracle , resort to Admin of Oracle , there is a dictionary table in Oracle to tell you if it is a date variable.

Contributor
Posts: 65

Re: to find if a nemeric variable is a date?

Posted in reply to DMoovendhan

Thank you all for the response...

Sorry I didn't mention it, I am loading a sas dataet to the Oracle server.

If I am able to find the date variables from the nuemeric variables that would be verymuch helpful for me.

I don't want to copy the dataset of Schema, I want to create the code to the executed in oracle that would create an empty table as same as the dataset in SAS.

Regards,

Moovendhan

Super User
Posts: 7,866

Re: to find if a nemeric variable is a date?

Posted in reply to DMoovendhan

Does this mean you want to automatically create code from the structure of SAS tables so you can load them into Oracle?

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 65

Re: to find if a nemeric variable is a date?

Posted in reply to KurtBremser

Yes

Super User
Super User
Posts: 7,997

Re: to find if a nemeric variable is a date?

Posted in reply to DMoovendhan

Well, that's pretty straight forward.  Isolate the required metadata in sashelp.vcolumns (or dictionary tables), you can also generate the code from there.

Super User
Posts: 10,046

Re: to find if a nemeric variable is a date?

Posted in reply to DMoovendhan

So it actually is a SAS table ? and follow SASKiwi mentioned , check the variable's informat and format .

Contributor
Posts: 65

Re: to find if a nemeric variable is a date?

Yes but the issue is, there are n number of sas datet formats, and it will not be possible to check for all the sas date formats.

I case if there is any alternative thjat would help..

Super User
Super User
Posts: 7,997

Re: to find if a nemeric variable is a date?

Posted in reply to DMoovendhan

I don't see the issue.  Pull the information from the metadata tables, that is what they are for.  If there are many then do a loop, or macro.  Post some example data and what you want and we can show you how to do it.  There are many examples of using SAS metadata to generate code which I have posted here using call execute - you can search for it.

Super User
Posts: 10,046

Re: to find if a nemeric variable is a date?

Posted in reply to DMoovendhan

Actually there are not too many date format , about fifty I guess.

Contributor
Posts: 65

Re: to find if a nemeric variable is a date?

There are about 123 date formats as of now that I collected, including week number formats etc...

For now I am Using SASKiwi's steps, as you recomonded.

I want this to work for user defined formats as well, just not so clear on that part.

Super User
Posts: 7,866

Re: to find if a nemeric variable is a date?

Posted in reply to DMoovendhan

SAS only knows two data types, numeric and character. There is no isdate() function or similar, so you have to DIY.

Making users adhere to standards is paramount if you want to make your job less of a hassle.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
🔒 This topic is solved and locked.

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

Discussion stats
  • 15 replies
  • 813 views
  • 5 likes
  • 5 in conversation