sas >> Excel Export keep leading zeros

by jgabbert » Sat, 07 Jun 2008 03:41:54 GMT

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

sas >> Excel Export keep leading zeros

by msz03 » Sat, 07 Jun 2008 04:41:56 GMT


hi ... if you want to try a different approach, use the Excel libname engine and the spreadsheet
will create a text variable in Excel with leading zeros ...

data test;
input num char : $8.;
datalines;
00000001 00000001
99999999 99999999
;
run;

libname tryit 'z:\newfile.xls';

data tryit.sheet1;
set test;
run;

libname tryit clear;

sas >> Excel Export keep leading zeros

by ben.powell » Mon, 09 Jun 2008 18:24:27 GMT

i, I use this macro for that reason. The standard ODS xls output is html
dressed up as xls which includes some odd behaviour that could confuse end
users, so I resave the file as type excel workbook at the end. I'm afraid
its pretty sparsely commented. The sub macro libtoview could probably be
debugged away - basically if a lib dataset other than from work is used it
creates a local copy to work from. Not doing so produced some odd behaviour
but as the mods indicate I haven't looked at is for over a year since I got
the idea off the list. I would benefit from having more interaction in
specifying variable order but I've not done this. You'll need to reference
your own html style or comment out my one. :)

/*
mac3_excel_text_ods.sas
=======================

Created BP 05FEB2007.
=====================

SAS Version: 9.13
-----------------
OS: WinXP Pro SP2
-----------------

Objectives:
-----------
Base SAS ODS html export that writes to Excel format xls without
unwanted html baggage; text formatting of any specified
character variables, or ALL.

Modifications:
--------------
BP 06FEB2007 Makes local copy of library dataset where applicable.


*/


%macro mac3_excel_text_ods(ds=,char=,path=,filename=,title=);
%let nonchar=;
%let chall=0;
%put NOTE: char=&char ....;

%macro libtoview();
data _libtoview;
a="&ds";
b=scan(a,-1,".");
keep b;
run;

proc sql noprint;
select b
into :xltxtods_v
from _libtoview;
create view v_&xltxtods_v as
select *
from &ds;
quit;

%let ds=&xltxtods_v;

data &ds;
set v_&xltxtods_v;
run;

proc sql;
drop view v_&xltxtods_v;
drop table _libtoview;
quit;

%put WARNING: lib dataset converted to work dataset...;
%mend;

data _libtoview;
a="&ds";
b=index(a,".");
if b>0 then x=1;
else x=0;
keep x;
run;

proc sql;
select x
into :l2v
from _libtoview;
quit;

%if &l2v=1 %then %libtoview();
%else %put NOTE: No lib dataset detected, execute as normal...;

/*RETAINS ORIGINAL COLUMN ORDER OF NON-TEXT VARIABLES; OTHERWISE
ALPHABETIC.*/
%inc 'P:\SAS\Macros2\mac2_getvarnum.sas';
%mac2_getvarnum(libname=work,ds=&ds);

proc sort data = v_tmpvarnum (keep=name varnum) out=_&ds._tmp;by name;run;

data _&ds._tmp;
set _&ds._tmp;
name=upcase(name);
keep name varnum;
run;

data _tmp_prs;
a="&char";
qty=countc("&char"," ")+1;
do i=1 to qty;
x=scan(a,i);
output;
end;
keep x;
run;

data _tmp_prs;
length name $32.;
format name $32.;
informat name $32.;
set _tmp_prs;
x=upcase(x);
name=x;
if x="ALL" then do;
call execute('
%let chall=1;
');
end;
run;

proc sort data = _tmp_prs;by x;run;

%macro mall();
data _NULL_;
call execute('
data _tmp_prs;
set _&ds._tmp;
x=name;
run;

proc sql;
select name
into :char separated by " "
from _&ds._tmp;
%put &char;
quit;
');
run;
%mend;
%if &chall=1 %then %mall;
%else %put NOTE: Not ALL ...;

proc sort data = _&ds._tmp;by name;run;
proc sort data = _tmp_prs;by name;run;

data _&ds._tmp;
merge _&ds._tmp _tmp_prs;
by name;
if x="";
keep name varnum;
run;

proc sort data = _&ds._tmp;by varnum;run;

proc sql;
select name
into :nonchar separated by ' '
from _&ds._tmp;
%put NOTE: nonchar=&nonchar ....;
%put;
drop table _&ds._tmp, _tmp_prs;
quit;

ods results off;
ods html style = SASdocVerySmall;
ods html file="&path\&filename" (no_top_matter);
ods html style = SASdocVerySmall;
title "&title";
proc print data=&ds

sas >> Excel Export keep leading zeros

by ben.powell » Mon, 09 Jun 2008 18:26:15 GMT

btw, if you include the macro usage is as below:

%inc 'P:\SAS\Macros3\mac3_excel_text_ods.sas';
%mac3_excel_text_ods
(ds=D204.D204_final,char=isn sitecode,/*TRY ALL.*/
path=c:\temp,
filename=temp.xls,
title=Title);

sas >> Excel Export keep leading zeros

by RolandRB » Mon, 09 Jun 2008 19:30:59 GMT


You can use a "style" in a "headtext" statement that will tell it to
keep the fields as text.

ods html file="name-of-file" headtext="<style> td {mso-number-format:
\@}</style>";

sas >> Excel Export keep leading zeros

by ben.powell » Mon, 09 Jun 2008 21:25:34 GMT

Apologies apparently I didn't declare one of the submacros:

%macro mac2_getvarnum(libname=,ds=);
proc sql;
create view v_tmpvarnum as
select libname, memname, memtype,
name, type, length,
label, format, npos, varnum
from dictionary.columns
where libname = "%upcase(&libname.)"
and memtype = 'DATA'
and memname eq "%upcase(&ds.)";
quit;
%mend;

sas >> Excel Export keep leading zeros

by pstloui » Mon, 09 Jun 2008 22:26:48 GMT

You've received lots of good advice. Here is one more way. In your list of
variables, include a separate line for this particular variable with a
tagattr statement:

VAR VAR1 VAR2 VAR3;
VAR VAR4 / STYLE(DATA)=[TAGATTR="000000000"];
VAR VAR5 VAR6;

/* Alter the number of zeros to what you need. */

In case you haven't read these, they are good resources:

"The Devil Is in the Detials: Styles, Tips, and Tricks That Make Your
Microsoft Excel Output Look Great" by Eric gebhart

"Tips and Tricks for Creating Multi-Sheet Microsoft Excel Workbooks the
Easy Way with SAS" by Vincent DelGobbo.

Similar Threads

1. ODS HTML: Keep Leading Zeroes when exporting to Excel

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

I have created windows application in VB.Net for export excel functionality 
from datatable. 
For this I used "Interop.Excel.dll" and "Interop.Office.dll" and created 
worksheet as below

Dim excelBook As Excel.Workbook = excelApp.Workbooks.Add

 

Dim excelWorksheet As Excel.Worksheet = CType(excelBook.Worksheets(1), 
Excel.Worksheet).



 

For cntDs As Integer = 0 To dsExport.Tables.Count - 1 

 


With excelWorksheet
....
...


 

End With

 

Next 

Its working fine. But once the data opened in the excel sheet (excel 2007), 
if there is any number with leading zero (eg:00123), it will be truncated 
which i dont want.

Even though if I change the format of excel sheet as text after loading the 
data, the truncated zeros are not coming. Is there any property in excel 2007 
to stop truncating leading zeros? Please advise.

Thanks in advance,
HR.


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

4. 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?

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

6. 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. 

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

8. Crystal Reports Export to Excel - Retaining Leading Zeros

Crystal Enterprise is the web-based front-end server which displays 
stored/linked Crystal reports to my target audience.  I use an export 
function that sends the report data to a .csv file so my users can open and 
manipulate it in Excel.  The file I'm trying to create MUST maintain the data 
types of the underlying data fields, but currently is not retaining leading 
zeros.

Leading zeros in various fields (ID#/SSN, Zip Codes, etc.) are not 
recognized and retained by Excel because it seems the incoming field data is 
seen as numerical instead of text.  Using known pre-Excel formatting tricks 
employing various combinations of using an apostrophe before the data, 
enclosing the data in quotation marks (before and after), and using space(s) 
in combination with the ' or " characters proves ineffective.

I am trying to correctly force-format the data containing leading zeros as 
TEXT, BEFORE it reaches Excel, in order to retain these leading zeros.

Please offer assistance or solutions.  Your help is most appreciated.  Thanks.