sas >> ODS HTML: Keep Leading Zeroes when exporting to Excel

by SS » Fri, 07 Aug 2009 21:15:44 GMT

Hi All,

I have been looking for a way to solve the problem of keeping leading
zeroes when exporting SAS data to an Excel file.

There are many resources such as:

1.http://www.tek-tips.com/viewthread.cfm?qid=1355208&page=10
2.
http://www.google.com.au/url?sa=t&source=web&ct=res&cd=9&url=http%3A%2F%2Fwww2.sas.com%2Fproceedings%2Fsugi28%2F012-28.pdf&ei=HSR8Sp2rB4iIkAXcxuT6Ag&rct=j&q=keep+leading+zeroes+ods+sas&usg=AFQjCNFR44oR5U1o2THTHmvkRK2alGIIqQ


I have constructed an example, but even this does not work:

/* Apply text format to all cells */
data one;
input acc_no zipcode;
cards;
0111 023560
0333 023334
;
run;

ODS HTML FILE="C:\test leading
zeroes.xls"
HEADTEXT="<STYLE> .zero {MSO-NUMBER-FORMAT:\@}</STYLE>";
TITLE;
PROC PRINT DATA=one NOOBS;
var acc_no / style={htmlclass="zero"};
RUN;
ODS HTML CLOSE;

acc_no still outputs as "111" instead of "0111" for example.

Could anyone please explain where I am going wrong here?

Thanks

sas >> ODS HTML: Keep Leading Zeroes when exporting to Excel

by SS » Fri, 07 Aug 2009 21:26:06 GMT



> HEADTEXT=< .zero {MSO-NUMBER-FORMAT:\@<"; >> TITLE;> > PROC PRINT DATA=one NOOBS>
> var acc_no / style={htmlclass="ze>o"};
> > RUN;
> ODS HTML >LO>E;
>
> acc_no still outputs as "111" instead of "0111" for ex>mp>e.
>
> Could anyone please explain where I am going wrong>he>e?
>
> Thanks

Sorry, I meant to say, I copied and pasted the gven example from the
links provided.

sas >> ODS HTML: Keep Leading Zeroes when exporting to Excel

by akshaya.nathilvar » Fri, 07 Aug 2009 23:21:49 GMT

Try with Excel tagsets in ODS:

ods tagsets.excelxp file='c:\abc.xml';
ods listing close;

Proc print noobs label;
var acc_no/style={tagattr='0000'} ;
var zipcode/style={tagattr='000000'};
Run;

ods tagsets.excelxp close;
ods listing;

AkshayA!

Similar Threads

1. Excel Export keep leading zeros

Hello,

=20

I'm creating a excel file using ODS and I am trying to maintain leading
zeros in one of the fields.  I created it as a character field in SAS
but in excel it reads it as numeric and cuts off the leading zeros.

=20

Any suggestions?

=20

Thanks in advance,

=20

Jessica

2. Export Excel tuncating leading zeros while export to excel from da

3. ODS html excel export with text formatting...

Dear List

Following several thread on the list I've got close to a holy grail for non
SAS/PC FORMATS licensees in the form of the following (Alan Churchill
deprecated) broken code:

data test;
infile datalines dlm = "," dsd missover;
informat isn $13.;
input text numeric;
cards;
0080252397,1
0180252397,1
2380252397,1
0300252393,1
0000252397,1
;
run;

%macro mac2_excel_text_ods(ds=,char=,path=,filename=,title=);
ods results off;
*ods html style = SASdocVerySmall;
ods html file="&path\&filename" (no_top_matter);
title "&title";
proc print data=&ds;
var &char / style(data)={htmlstyle= "mso-number-format:\@"};
run;
title;
ods html close;

options noxwait;
x "&path\&filename";

filename excelsys  dde "excel|system";
data _null_;
file excelsys;
put '[FileSaveAs .Name = "'"&path.&filename"'"
 .Type=ConverterLookup("Microsoft Office Excel Workbook")]';
put '[Fileclose]';
run;
%mend;

%mac2_excel_text_ods
 (ds=test,char=text,path=c:\temp,filename=temp.xls,title=Example
xls);


Can someone resolve the following log message?


NOTE: The file EXCELSYS is:
      DDE Session,
      SESSION=excel|system,RECFM=V,LRECL=256

ERROR: DDE session not ready.
FATAL: Unrecoverable I/O error detected in the execution of the data step
program.
       Aborted during the EXECUTION phase.
NOTE: 0 records were written to the file EXCELSYS.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: DATA statement used (Total process time):
      real time           0.06 seconds
      cpu time            0.01 seconds


The problem is with the attempted DDE conversion of the SAS output html
flavour of a workbook (web page suffixed xls) to a real Workbook format xls.

Any comments much appreciated,


Ben.

4. import excel file into access and keep leading zeros for data 052 - External Data - Access

5. how can I keep excel from suppressing leading zeros as text

i am having a problem with Office 2007.  It deletes leading zeros off of my 
numbers stored as text.  how can I make it stop doing this?

6. Importing to Excel from csv AND keep leading zeros - Excel

7. Keeping lead zeros in excel text format when saving as CSV file

I am working in excel and need to import into Sage.  I can create the CSV 
file that is required but I lose the leading zeros from certain text 
columns/fields when I convert to from excel to CSV (for example 025 becomes 
25).  How can I overcome this.  If I edit the CSV file in Notepad will this 
do the trick. If it does, this will be OK for the odd item, but I may need to 
do it for hundreds of items in the same column/field and this is impractical 
in Notepad. 

8. DTS import Excel to SQL Server, keep leading zeroes - SQL Server