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 2009, week 1)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
Date:         Wed, 6 May 2009 09:56:04 -0700
Reply-To:     jfh@stanfordalumni.org
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Jack Hamilton <jfh@STANFORDALUMNI.ORG>
Subject:      Re: SASDATEFMT in PROC SQL
Comments: To: mahesh kumar peesari <peesari.mahesh@gmail.com>
In-Reply-To:  <583d10d00905060837h3d52a9ffp11c989df9129226d@mail.gmail.com>
Content-Type: text/plain; charset="ISO-8859-1"

Do you want the value in the Oracle table to contain only the date, or both the date and the time? What you want is not clear to me.

If I have an Oracle table containing a numeric field MyID and a date field MyDate, this code appears to work:

===== libname mydata oracle user=c449630 password=filegod_arcat01 path='@arcat01' schema='c449630';

data temp;

myid = 1; mydate = datetime(); format mydate datetime.;

run;

proc sql;

insert into mydata.test select myid, mydate from temp ; quit; =====

When I look at the table in SQL Developer, the date field is displayed as "2009-05-06". If I change the display format in Oracle to "yyyy-mm-dd HH.MI", it is displayed as "2009-05-06 09.53".

On Wed, 6 May 2009 21:07:33 +0530, "mahesh kumar peesari" <peesari.mahesh@gmail.com> said: > HI Jack, > > I dont even know how i have got two names may be while i have registered > something went wrong..so that whenever i post ..its giving JOE H SMITH. > > And coming to requirement.I am using SAS DI...so i have got many ETL jobs > there.so i have to write a postprocess code,and i will save all my code > in > postprocess.so whenever my job runs this postprocess code creates a > temporary data set where it has jobname and the date on which job is > run,and > using proc sql i am populating this data set into oracle table.so the > issue > is date value in SAS is stored as dtdate9. but when itis populated into > oracle table it is not stored as just date value,but it is taking > datetime > value.so when i have googled i have got thiis SASDATEFMT,which converts > date > into the desired format > what exactly i am using i.e if i am using dtdate9.the date values will be > just populated as dtdate9. format but not as datetime values...i dontknow > exactly how it works i have just taken from google and placed as it is > used > there ..but it doesnt workout..it is throwing error...any help as how to > use > SASDATEFMT or > how can i populate just date value from dataset to oracle table with > only > date values and not with datetime value. > > Hope i confusing you..really sorry.... > Please give me any inputs.. > > Thanks & Regards > > > On Wed, May 6, 2009 at 8:46 PM, Jack Hamilton > <jfh@alumni.stanford.org>wrote: > > > Your question isn't clear to me. And the documentation for the > > SASDATEFMT data set option isn't clear either. I think you'll have to > > experiment. That's much faster than waiting for an answer from SAS-L. > > What happens if you code "coderun=date9." instead of "coderun=dtdate9."? > > > > And why do you have two different personal names, "peesari.mahesh" and > > "joe h smith"? > > > > > > On Wed, 6 May 2009 08:45:49 -0400, "SUBSCRIBE SAS-L Joe H. Smith" > > <peesari.mahesh@GMAIL.COM> said: > > > Hi all , > > > > > > Here is my code: > > > data work.TEMP; > > > /* ProcessingDate_D=date()*86400;*/ > > > ProcessingDate_D=datetime(); > > > > > > JobName_V='Job1'; > > > /* Startdate_D=input("&SYSDATE9",) DATETIME22.3;*/ > > > /* EndDate_D=datetime();*/ > > > /* format ProcessingDate_D dtdate9.;*/ > > > format ProcessingDate_D dtdate9.; > > > run; > > > proc sql noerrorstop; > > > INSERT INTO DBVYM.etltestjob (sasdatefmt=(CodeRun=dtdate9.)) > > > (Job,CodeRun ) > > > SELECT JobName_V,ProcessingDate_D FROM work.TEMP ; > > > quit; > > > > > > my requirement is i have to populate table in oracle from SAS dataset,But > > > there is a date field(ProcessingDate_D) in my SAS dataset,where while > > > populating, is using the entire datetime value,if i check the data set > > > in > > > oracle defined library it is stored as entire datetime value but not as > > > only date,I have tried to use sasdatefmt option but still cant suceed,can > > > you please help me how i have to use sasdatefmt ,so that only date value > > > gets populated and not the entire datetime value. > > > > > > Thanks in Advance. > > > > > > -- > > Jack Hamilton > > Sacramento, California > > jfh@alumni.stanford.org <== Use this, not jfh @ stanfordalumni.org > > > > > > > -- > Jack Of All Trades....But Master Of NONE....

-- Jack Hamilton Sacramento, California jfh@alumni.stanford.org <== Use this, not jfh @ stanfordalumni.org

Tots units fem força!


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