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!