Neil,

Unless a number of things have changed in 9.2, I'd guess that you and
SAS are simply interpreting your statements differently.

A number of things:

1. I don't think the proc export command is supposed to end with a
semi-colon.
2. when you specified dmbs did you mean dbms?
3. I don't think that the xls engine supports the sheet= option
4. I think that the xls engine formats all dates and datetimes as
date9 unless you set the sas system option (I'm not sure if I remember
the correct option name but it is something like) sasdatefmt.

HTH,
Art
-------------
On Feb 27, 5:33 am, neil <nei...@gmail.com> wrote:
> I think it's very weird. And it's very easy to reproduce the problem.
>
> SAS 9.2 (32-bit/64-bit)
>
> proc export data=whatever_dataset_with_datetime;
> outfile='path\filename'
> dmbs=xls replace;
> sheet='01';
> run;
>
> Note: the reason I am using "dbms=xls" instead of "dbms=excel" is xls
> is only option in 64-bit sas. This "XLS" causes the following problem.
>
> After you submit it, everything is fine.
>
> OPEN the excel and SWITCH two columns, THEN SAVE it.
>
> run the same code again but with sheet='02', open the excel file, you
> will find the date format in sheet02 is LOST.
>
> Do you know what's the fix?
>
> Thanks

1 and 2 my typo.
3, dbms=3Dxls does support sheet. Have you ever tried?
4. The problem is, after you open and change the excel file, then
export another dataset to the same excel file, the date format will be
lost.

On Feb 27, 9:35=A0am, art...@NETSCAPE.NET (Arthur Tabachneck) wrote:
> Neil,
>
> Unless a number of things have changed in 9.2, I'd guess that you and
> SAS are simply interpreting your statements differently.
>
> A number of things:
>
> 1. I don't think the proc export command is supposed to end with a
> semi-colon.
> 2. when you specified dmbs did you mean dbms?
> 3. I don't think that the xls engine supports the sheet=3D option
> 4. I think that the xls engine formats all dates and datetimes as
> date9 unless you set the sas system option (I'm not sure if I remember
> the correct option name but it is something like) sasdatefmt.
>
> HTH,
> Art
> -------------
> On Feb 27, 5:33 am, neil <nei...@gmail.com> wrote:
>
>
>
> > I think it's very weird. And it's very easy to reproduce the problem.
>
> > SAS 9.2 (32-bit/64-bit)
>
> > proc export data=3Dwhatever_dataset_with_datetime;
> > outfile=3D'path\filename'
> > dmbs=3Dxls replace;
> > sheet=3D'01';
> > run;
>
> > Note: the reason I am using "dbms=3Dxls" instead of "dbms=3Dexcel" is x=
ls
> > is only option in 64-bit sas. This "XLS" causes the following problem.
>
> > After you submit it, everything is fine.
>
> > OPEN the excel and SWITCH two columns, THEN SAVE it.
>
> > run the same code again but with sheet=3D'02', open the excel file, you
> > will find the date format in sheet02 is LOST.
>
> > Do you know what's the fix?
>
> > Thanks

Neil -
  I did not quite follow your scenario, but it sounds like you trying
to export data into existing cells.
  If so then the behavior you describe sounds like what I would want
SAS to do.  If I exported numbers into a cell that already had a
format defined for it in Excel I would not want SAS to override that
format.
- Tom

On Feb 27, 7:41=A0pm, neil <nei...@gmail.com> wrote:
> 1 and 2 my typo.
> 3, dbms=3Dxls does support sheet. Have you ever tried?
> 4. The problem is, after you open and change the excel file, then
> export another dataset to the same excel file, the date format will be
> lost.
>
> On Feb 27, 9:35=A0am, art...@NETSCAPE.NET (Arthur Tabachneck) wrote:
>
>
>
> > Neil,
>
> > Unless a number of things have changed in 9.2, I'd guess that you and
> > SAS are simply interpreting your statements differently.
>
> > A number of things:
>
> > 1. I don't think the proc export command is supposed to end with a
> > semi-colon.
> > 2. when you specified dmbs did you mean dbms?
> > 3. I don't think that the xls engine supports the sheet=3D option
> > 4. I think that the xls engine formats all dates and datetimes as
> > date9 unless you set the sas system option (I'm not sure if I remember
> > the correct option name but it is something like) sasdatefmt.
>
> > HTH,
> > Art
> > -------------
> > On Feb 27, 5:33 am, neil <nei...@gmail.com> wrote:
>
> > > I think it's very weird. And it's very easy to reproduce the problem.
>
> > > SAS 9.2 (32-bit/64-bit)
>
> > > proc export data=3Dwhatever_dataset_with_datetime;
> > > outfile=3D'path\filename'
> > > dmbs=3Dxls replace;
> > > sheet=3D'01';
> > > run;
>
> > > Note: the reason I am using "dbms=3Dxls" instead of "dbms=3Dexcel" is=
 xls
> > > is only option in 64-bit sas. This "XLS" causes the following problem=
..
>
> > > After you submit it, everything is fine.
>
> > > OPEN the excel and SWITCH two columns, THEN SAVE it.
>
> > > run the same code again but with sheet=3D'02', open the excel file, y=
ou
> > > will find the date format in sheet02 is LOST.
>
> > > Do you know what's the fix?
>
> > > Thanks- Hide quoted text -
>
> - Show quoted text -