11-22-2013 03:35 PM
This likely a simple question...
I have dates within a database formatted mm/dd/yyyy, and I would like to convert the date to the first of every month within a proc sql pull, retaining the date format.
For example, I want 10/8/2013 to show up as 10/1/2013.
11-22-2013 04:17 PM
The format won't change unless you explicitly change it, so no worry there.
select mdy(month(datevariable),1,Year(datevariable)) as DateVariable
should do it.
11-22-2013 05:59 PM
ballardw - Unfortunately, that method didn't work - mdy is not a function in Teradata sql apparently. Sorry for not making that clear in the original post.
Fugue - I am using SQL through Teradata
Thanks for the help!
11-22-2013 06:08 PM
Are you using pass-through or libname access? Functions used in the proc sql "pull" may depend on whether the query is pass-through vs libname.
Is the variable one of the supported Teradata data/time types identified at: http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#a001384390.htm?? Or, is the date a character string?
11-22-2013 07:50 PM
In SAS you would use intnx() for shifting date, time or datetime values. Unfortunately intnx() is not one of the functions which gets pushed to Teradata SAS/ACCESS(R) 9.4 for Relational Databases: Reference, Second Edition
You will need explicit SQL pass-through and use Teradata SQL functions for this.
"Googling" using the following keywords "teradata how to shift date to beginning of month" the following came up:
11-23-2013 03:58 AM
There is something to know about teradata dates. ..... It is the same approach as SAS is using.
Search on the internal representation at Teradata (14.0)
The teradata SQL specfics on their forums: Date and Time Manipulation on Teradata
When installed in a good practice approach formats of SAS can be handled as in database procedures.
You shoudl have them available. SAS(R) 9.3 In-Database Products: User's Guide
Using the SAS/Access interface datatypes are translated as documented by: SAS/ACCESS(R) 9.3 for Relational Databases: Reference, Second Edition
And having the fucntions translated from SAS to teradata: SAS/ACCESS(R) 9.3 for Relational Databases: Reference, Second Edition
If you are just retrieving the data .... adjsut them after you got your results in SAS