Hi,

I am pretty new to the excelxp taget world, but am finding my way.  My
current prob is I am using a proc print, the member_var is a
character, but only has numerics.  So when ods outputs it to xlm the
data type is numeric instead of string.  I need it to be string so if
someone saves the excel document I dont lose the leading zeros of the
member_var.  How can I change the data type of a variable when it
writes it to xml.  My code is below.

ods tagsets.ExcelXP path="xxx"
                    file="test.xls");
proc print data = test_dset noobs;
var member_var update;
run;
ods tagsets.ExcelXP close;

"Captain" <apsteinberg@hotmail.com> wrote in message
news:06f73064-9776-40fd-abb6-47a9b18ebe3a@r1g2000yqj.googlegroups.com...
> Hi,
>
> I am pretty new to the excelxp taget world, but am finding my way.  My
> current prob is I am using a proc print, the member_var is a
> character, but only has numerics.  So when ods outputs it to xlm the
> data type is numeric instead of string.  I need it to be string so if
> someone saves the excel document I dont lose the leading zeros of the
> member_var.  How can I change the data type of a variable when it
> writes it to xml.  My code is below.
>
> ods tagsets.ExcelXP path="xxx"
>                     file="test.xls");
> proc print data = test_dset noobs;
> var member_var update;
> run;
> ods tagsets.ExcelXP close;

I don't have a solution, only a comment/observation.  It probably isn't SAS
or XML that's changing the data type from character to numeric.  Most likely
it's Excel.  If you open the xml file in a text editor, my bet is that you
won't see any difference between character and numeric values in the xml
file.

Excel assigns a 'general' data type to most cells - values consisting of
numerals only will be displayed as numbers while values with non-numerals
will be displayed as text.  If the value contains something that makes it
"obvious" that some other data type should be applied, it will apply that
data type - for instance if the value in a cell is "$1" Excel assigns a type
of Currency to that cell, while if the value is "03-21-2010" a data type of
Date is assigned.  The fact that all values may have been character in SAS
doesn't matter - Excell doesn't know anything about SAS.

For values that consist of numerals only that you want to display in a
particular format, Excel allows you to define a Custom data type for display
(in Excel, click on Format > Cells > Custom).  To display "1" as "01.00" for
instance, in the "Type" box on the Custom Format dialog, you'd put "00.00"
(without the quotes, of course).  The custom formatting is saved when you
save the sheet, so everyone sees it that way (unless someone changes the
data type definition) but as far as Excel is concerned, the value is still
numeric.

You can pass formatting instructions like this through to your XML file when
using ODS.  If you were using PROC REPORT, it would look something like:

PROC REPORT DATA = FEE NOWD;
   COLUMN FIE FOE;
   DEFINE FIE / DISPLAY CENTER 'First Variable' STYLE = {TAGATTR =
"\00.00"};
   DEFINE FOE / DISPLAY CENTER 'Second Variable";
QUIT;

Presumably, something of the sort would work in PROC PRINT as well - I've
never tried.  One last caveat - you don't say what release of SAS you're
using, when it was installed, or what release of Excel you have.  Depending
on what you're trying to do, you may need to download and install the latest
version of the tagset from the SAS site.

I found that one before. You can use the STYLE option on the VAR
statement to set the Excel formatting. Also you can use multiple VAR
statements.

proc print ..... ;
  var member_var /style=3D{tagattr=3D'format:text'} ;
  var update;
run;



On Mar 24, 2:22=A0pm, Captain <apsteinb...@hotmail.com> wrote:
> Hi,
>
> I am pretty new to the excelxp taget world, but am finding my way. =A0My
> current prob is I am using a proc print, the member_var is a
> character, but only has numerics. =A0So when ods outputs it to xlm the
> data type is numeric instead of string. =A0I need it to be string so if
> someone saves the excel document I dont lose the leading zeros of the
> member_var. =A0How can I change the data type of a variable when it
> writes it to xml. =A0My code is below.
>
> ods tagsets.ExcelXP path=3D"xxx"
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 file=3D"test.xls");
> proc print data =3D test_dset noobs;
> var member_var update;
> run;
> ods tagsets.ExcelXP close;