LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (May 1997, week 3)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
Date:   Fri, 16 May 1997 05:31:44 GMT
Reply-To:   Floyd Lunt <luntf@NETROPOLIS.NET>
Sender:   "SAS(r) Discussion" <SAS-L@UGA.CC.UGA.EDU>
From:   Floyd Lunt <luntf@NETROPOLIS.NET>
Subject:   Re: Proc SQL Question:Dates
Content-Type:   text/plain; charset=us-ascii

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


Back to: Top of message | Previous page | Main SAS-L page