BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
DMoovendhan
Quartz | Level 8


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

1 ACCEPTED SOLUTION

Accepted Solutions
SASKiwi
PROC Star

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

15 REPLIES 15
SASKiwi
PROC Star

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Super User

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
Quartz | Level 8

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Super User

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

DMoovendhan
Quartz | Level 8

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Super User

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

DMoovendhan
Quartz | Level 8

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
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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