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