Date: Wed, 6 May 2009 09:56:04 -0700
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Jack Hamilton <jfh@STANFORDALUMNI.ORG>
Subject: Re: SASDATEFMT in PROC SQL
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
myid = 1;
mydate = datetime();
format mydate datetime.;
insert into mydata.test
select myid, mydate
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"
> 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
> 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
> using proc sql i am populating this data set into oracle table.so the
> 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
> value.so when i have googled i have got thiis SASDATEFMT,which converts
> 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
> there ..but it doesnt workout..it is throwing error...any help as how to
> SASDATEFMT or
> how can i populate just date value from dataset to oracle table with
> 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
> > 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
> > email@example.com <== Use this, not jfh @ stanfordalumni.org
> Jack Of All Trades....But Master Of NONE....
firstname.lastname@example.org <== Use this, not jfh @ stanfordalumni.org
Tots units fem força!