Here is another way to do the same thing.

proc sql; CONNECT TO ORACLE;

create table dateconv as select pid,visit,datepart(oradate) as sasdate from connection to oracle (select pid,visit, oradate from &protocol..crf_&db where prot=&study);

DISCONNECT FROM ORACLE; quit;

On Thu, 08 May 1997 10:24:39 -0700, Roland <RolandRB@nospam.netcomuk.co.uk> wrote:

>You have to do a double conversion. Convert it to text and then read it >in using an informat. The following example will give you a proper date. >There might be a way using the to_date function as well. The following >works though. > >%let protocol=sbbrl29060_idr_cdf; >%let study=448; >%let db=data@uspat; > >proc sql; > CONNECT TO ORACLE; > > create table dateconv as > select pid,visit,input(dat,mmddyy8.) as dat > from connection to oracle > (select pid,visit,to_char(dat,'MMDDYYYY') dat > from &protocol..crf_&db > where prot=&study); > > DISCONNECT FROM ORACLE; >quit; > > > >Luvyellab wrote: >> >> I am trying to create a SAS dataset using an Oracle table and load it to >> the PC so I can use DBMS copy to convert the dataset to a STATA file. I >> am using Proc SQL to create the SAS dataset. The format on the Oracle >> database is in date time(01-Jan-1997). I need to convert it to a mmddyy >> format with no time.. Is there a function I can use in Proc SQL to do >> this so the variable is written out as a date and can be accessed as a >> date variable in SAS, or should I create the SAS dataset and then use the >> SAS date formats? I have tried the to_char function: >> to_char(fromdt,'mmddyyyy') but this changes the variable to a character >> variable 75 characters in length. I have a large dataset with many dates >> and 75 characters per date, makes the dataset too large. I would >> appreciate any suggestions. Thanks. >> Kaye Hendry >> HealthInsight >> Systems Analyst