Prev: Robust standard errors in Zero inflated Poisson and Zero inflated
Next: Reading an XML parsed file
From: neil on 27 Feb 2010 05:33
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
From: Arthur Tabachneck on 27 Feb 2010 09:35
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...(a)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
From: neil on 27 Feb 2010 19:41
1 and 2 my typo.
3, dbms=xls 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 am, art...(a)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= 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...(a)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

From: Tom Abernathy on 28 Feb 2010 10:28
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 pm, neil <nei...(a)gmail.com> wrote:
> 1 and 2 my typo.
> 3, dbms=xls 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 am, art...(a)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= 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...(a)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- Hide quoted text -
>
> - Show quoted text -

From: Arthur Tabachneck on 28 Feb 2010 11:38
Neil,

Regarding point 3, yes I have tried it, but only on 9.1.3. On 9.1.3
my log shows the following:

79 sheet='01';
-----
180

ERROR 180-322: Statement is not valid or it is used out of proper
order.

Is it now possible with 9.2? If not, are you simply overwriting
sheet1?

Art
------------
On Feb 27, 7:41 pm, neil <nei...(a)gmail.com> wrote:
> 1 and 2 my typo.
> 3, dbms=xls 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 am, art...(a)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= 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...(a)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- Hide quoted text -
>
> - Show quoted text -
 |  Next  |  Last
Pages: 1 2
Prev: Robust standard errors in Zero inflated Poisson and Zero inflated
Next: Reading an XML parsed file