to find if a nemeric variable is a date?

[DMoovendhan]

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

[Kurt_Bremser]

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

[SASKiwi · Accepted solution]

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.

[RW9]

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.

[Ksharp]

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.

[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

[Kurt_Bremser]

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

[DMoovendhan]

Yes

[RW9]

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

[Ksharp]

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

[DMoovendhan]

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..

[RW9]

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.

[Ksharp]

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

[DMoovendhan]

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.

[Kurt_Bremser]

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.