Prev: mainframe SAS 9.2
Next: histogram
From: SAS User on 18 Jun 2010 10:06
Hi,

Could I have some advice please as I need to convert a datetime field
to a date but I cannot use datepart() and need to use
PROC SQL.

Would it be something like this

substr(to_char(mydatetimefield,n,n,))

Thanks

Lee
From: SAS User on 18 Jun 2010 10:52
Specifically I would like to do this conversion without using
sql_functions=all and without causing data to be brought back to SAS
for processing from SQL Server.

http://support.sas.com/onlinedoc/913/docMainpage.jsp
Lee


From: Patrick on 18 Jun 2010 22:06
Hi Lee

to_char is an Oracle SQL function which converts a date, datetime...
to a string (varchar2).

As it's Oracle you could only use it in pass-through SQL.

If you're using SAS9.2 then the datepart() function should now be
passed to Oracle
http://support.sas.com/documentation/cdl/en/acreldb/63283/HTML/default/viewer.htm#/documentation/cdl/en/acreldb/63283/HTML/default/a003113612.htm

For previous SAS versions:

Use SAS formats and informats to convert a datetime to a date value as
SAS will be able to translate this into Oracle SQL and send it to the
DB.

Make sure the result of this conversion with formats and informats is
numeric for a Oracle date field or character for a Oracle varchar2
field.

Sometimes it's neccessary to apply a date format to a SAS date field
so that SAS knows to treat this as date and not simple numeric field
when passing the SQL to the DB (i.e. SELECT maydate format=date9.).

The code below to illustrate what I tried to describe. It's sometimes
a bit of try and error until you've got the syntax in a way that it's
fully passed to the DB. Use the following options to see what's passed
to Oracle:
options sastrace=',,,ds' sastraceloc=saslog nostsuffix;

data have;
do dttm='01jan2010 00:00:00'dt to '05jan2010 00:00:00'dt by 84000;
output;
end;
run;

proc sql;
select input(put(dttm,dtdate9.),date9.) format=date9.
from have
where input(put(dttm,dtdate9.),date9.) between '02jan2010'd and
'04jan2010'd
;
quit;


HTH
Patrick
From: SAS User on 20 Jun 2010 04:28
Patrick

Thanks for your response.

It was a mistake to use TO_CHAR in my example sorry as I am not using
an Oracle RDBMS but SQL Server with SAS Access to OLE DB (SAS 913).
The input and put functions are not passed to the RDBMS.

I can leave SQL functions=all but want to avoid using datepart for the
moment and would rather do it another way if I can.
Thanks
Lee







 | 
Pages: 1
Prev: mainframe SAS 9.2
Next: histogram