Date:         Mon, 31 Mar 2008 19:36:02 -0700
Reply-To:     jfh@stanfordalumni.org
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Jack Hamilton <jfh@STANFORDALUMNI.ORG>
Subject:      Re: Pushing formats into Excel
Comments: To: "data _null_," <datanull@GMAIL.COM>
In-Reply-To:  <7367b4e20803311847o2d2fc997mb1891b7dc02f980c@mail.gmail.com>
Content-Type: text/plain; charset="ISO-8859-1"

What didn't you like about tagsets.ExcelXP?

It can be difficult to fine-tune the appearance of ExcelXP output.  I
don't know whether that has improved in 9.2.



On Mon, 31 Mar 2008 20:47:53 -0500, "data _null_," <datanull@GMAIL.COM>
said:
> This technique may work well enough.  It uses PROC TRANSPOSE to
> convert all values to their formatted values.  All vars are converted
> to character.  Then transposed back and EXPORTed.  I fiddled with ODS
> TAGSETS.EXCEL which I think should work but I was not happy with the
> results.
>
> PROC FORMAT;
>   VALUE gender  1="Male" 2="Female";
>   VALUE make    2="Good" 3="Best";
>   VALUE type    1="Old" 2="New";
>   run;
> data work.test;
>   id = _n_;
>   input gender make type @@;
>   format gender gender. make make. type type.;
>   retain dummy ' ';
>   cards;
> 1 3 1 2 2 2 2 1 3
> ;;;;
>   run;
> proc transpose data=work.test out=work.flip(where=(_name_ ne 'dummy'));
>   by id;
>   var dummy gender make type;
>   run;
> proc transpose data=work.flip out=work.flop(drop=_name_);
>   by id;
>   var col1;
>   run;
> proc contents varnum;
> proc print;
>   run;
> PROC EXPORT DATA=flop OUTFILE="silica_dth_a.xls"
>
>    DBMS=EXCEL REPLACE;
>    SHEET="silica_pd_dth";
>  RUN;
>
> On Mon, Mar 31, 2008 at 5:40 PM, Laughing Beggar
> <laughing_beggar@hotmail.com> wrote:
> > Hi all,
> > Is there any way of keeping my formats when I push a dataset into Excel?
> > Here's what I'm doing:
> > DATA pd;
> >   SET work.silica_pd_dth;
> >   FORMAT
> >    slares $SLA.
> >    sex $SEX.
> >    icd10dth $icd10fc.;
> > RUN;
> > PROC EXPORT DATA= pd
> >              OUTFILE=
> >              "C:\temp\richard\silica_dth_a.xls"
> >              DBMS=EXCEL REPLACE;
> >     SHEET="silica_pd_dth";
> >  RUN;
> > Trouble is, the Excel file sheets cells have lost the formatting from the FORMAT statement in the data step :-(
> > Any way to fix this?
> > Cheers
> > L_B
> >
> > "The beggar laughs in the face of the thief"
> > _________________________________________________________________
> > It's simple! Sell your car for just $30 at CarPoint.com.au
> > http://a.ninemsn.com.au/b.aspx?URL=http%3A%2F%2Fsecure%2Dau%2Eimrworldwide%2Ecom%2Fcgi%2Dbin%2Fa%2Fci%5F450304%2Fet%5F2%2Fcg%5F801459%2Fpi%5F1004813%2Fai%5F859641&_t=762955845&_r=tig_OCT07&_m=EXT


--
Jack Hamilton
Sacramento, California
jfh@alumni.stanford.org