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 23:47:06 +0530
Reply-To:     mahesh kumar peesari <peesari.mahesh@GMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         mahesh kumar peesari <peesari.mahesh@GMAIL.COM>
Subject:      Re: SASDATEFMT in PROC SQL
Comments: To: jfh@stanfordalumni.org
In-Reply-To:  <1241628964.32056.1314120327@webmail.messagingengine.com>
Content-Type: text/plain; charset=ISO-8859-1

Thanx Jack i will give a try tomorrow..and let u know the outcome.....ok jack i have understood..but still fingers crossed....

I still want to have only date values in both tables and not datetime values.when i use dtdate9. format the values in oracle table is showing as 01/jan/1960 and not the current date...thanx a lot..i am killing u r time ..i will update tomorrow.

On Wed, May 6, 2009 at 10:26 PM, Jack Hamilton <jfh@alumni.stanford.org>wrote:

> 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! > >

-- Jack Of All Trades....But Master Of NONE....


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