topic Re: to find if a nemeric variable is a date? in SAS Programming

to find if a nemeric variable is a date?

DMoovendhan — Mon, 10 Nov 2014 07:34:49 GMT

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

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

Kurt_Bremser — Mon, 10 Nov 2014 08:04:37 GMT

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

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

SASKiwi — Mon, 10 Nov 2014 08:41:56 GMT

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.

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

RW9 — Mon, 10 Nov 2014 09:32:28 GMT

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.

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

Ksharp — Mon, 10 Nov 2014 09:42:06 GMT

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.

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

DMoovendhan — Mon, 10 Nov 2014 12:22:14 GMT

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

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

Kurt_Bremser — Mon, 10 Nov 2014 12:28:41 GMT

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

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

RW9 — Mon, 10 Nov 2014 13:05:55 GMT

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

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

Ksharp — Mon, 10 Nov 2014 13:06:11 GMT

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

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

DMoovendhan — Mon, 10 Nov 2014 13:51:46 GMT

Yes

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

DMoovendhan — Mon, 10 Nov 2014 13:53:18 GMT

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

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

RW9 — Mon, 10 Nov 2014 14:02:56 GMT

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.

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

Ksharp — Mon, 10 Nov 2014 14:14:04 GMT

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

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

DMoovendhan — Mon, 10 Nov 2014 15:21:58 GMT

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.

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

Kurt_Bremser — Mon, 10 Nov 2014 15:40:30 GMT

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.

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

DMoovendhan — Tue, 18 Nov 2014 06:31:51 GMT

Thank you Kurt, for the datasets that are gonnu be created, we have given clear instructions.

But for the previous datasets that were created with custom formats, we are creating the formats and then creating the metadata for the database ie in SQL.

For the newer versions this issue won't be there, hoping so