Date: Thu, 3 Jul 2003 16:05:51 -0400
Reply-To: Ernest Bowling <ernest.bowling@JWS.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Ernest Bowling <ernest.bowling@JWS.COM>
Subject: Re: Excel output file with label and numeric format problems
Helen,
You can format the numeric values as follows. Use whatever format you want
in place of best.
PROC DBLOAD DBMS=EXCEL DATA=class;
PATH='c:\temp\class.xls';
PUTNAMES=YES;
label;
format _numeric_ best.;
RESET ALL;
LOAD ;
RUN ;
I don't think proc dbload will overwrite a file. You must delete it first.
I use something like the following to delete a file. Place this code before
your proc dbload.
data _null_;
fname="tempfile";
rc = filename(fname,"c:\temp\class.xls");
if rc = 0 and fexist(fname) then
rc = fdelete(fname);
put rc;
rc=filename(fname);
put rc;
run;
Ernest
On Thu, 3 Jul 2003 10:58:35 -0700, Helen <sunchunkui@HOTMAIL.COM> wrote:
>Dear SAS-L,
>
>I would like to export sas to Excel with variable label and correct
>numeric values. By using Proc export, I can get correct numeric
>variable values, but no label. By using Proc dbload, I can get
>variable label in Excel but the numeric values are changed into
>xxxx.00 format which I don't like. Is there another way to make the
>output correctly? Thanks in advance.
>
>The following is an example:
>
>data class;
> set sashelp.class;
> label name='Student Name'
> sex='Gender'
> age='Age of 2003'
> weight='Weight of 2003'
> Height='Height of 2003';
>run;
>PROC DBLOAD DBMS=EXCEL DATA=class;
> PATH='c:\temp\class.xls';
> PUTNAMES=YES;
> label;
> RESET ALL;
> LOAD ;
>RUN ;
>
>Student Name Gender Age of 2003 Height of 2003 Weight of 2003
>Alfred M 14.00 69.00 112.50
>Alice F 13.00 56.50 84.00
>Barbara F 13.00 65.30 98.00
>Carol F 14.00 62.80 102.50
>Henry M 14.00 63.50 102.50
>James M 12.00 57.30 83.00
>Jane F 12.00 59.80 84.50
>Janet F 15.00 62.50 112.50
>Jeffrey M 13.00 62.50 84.00
>John M 12.00 59.00 99.50
>Joyce F 11.00 51.30 50.50
>Judy F 14.00 64.30 90.00
>Louise F 12.00 56.30 77.00
>Mary F 15.00 66.50 112.00
>Philip M 16.00 72.00 150.00
>Robert M 12.00 64.80 128.00
>Ronald M 15.00 67.00 133.00
>Thomas M 11.00 57.50 85.00
>William M 15.00 66.50 112.00
>
>By the way, when I run the above codes on Windows2000 sas8.2 first
>time, I had a warning: 'WARNING: Label set. Use reset command to use
>SAS labels.'
>When I run it second time, I got an error:
>'ERROR: The file, c:\temp\class.xls, already exists and will not be
>overwritten or is not on a direct access device.'
>How could I do to avoid the warning and can overwrite the previous
>file when I run the program?
>
>I really appreciate any comments.
>
>Helen
| 
