Proc export - Retain leading zeroes in Excel

[TJP]

Can you keep leading zeros in proc export when creating an excel file.  Don't want to create a cvs file or use proc print with ODS

[Reeza]

If it's a character variable it should stick. Formats are not passed to the Excel file. 

Otherwise ODS Excel is your best option. 

[Ksharp]

Add a TAB character before it, then proc export.

x=cats('09'x,x);

[art297]

I agree with @Reeza. The leading zeros should stay if you make it a character variable. e.g.:

data have;
  input var1;
  format var1 z6.;
  format var2 $6.;
  var2=put(var1,z6.);
  cards;
1
2
3
4
5
6
7
8
9
;


proc export data=have file='/folders/myfolders/want.xlsx' replace dbms=xlsx;
  sheet='Sheet1';
run;

Var2 should export as desired.

Art, CEO, AnalystFinder.com

[rogerjdeangelis]

You don't need all that 'proc export code'

* keep leading zeros;

libname xel clear;   * just in case you forget;
%utlfkil(d:/xls/have.xls); * delete it;
libname xel "d:/xls/have.xls";

data xel.have;
  input var1;
  format var1 z6.;
  format var2 $6.;
  var2=put(var1,z6.);
cards4;
1
2
3
4
5
6
7
8
9
;;;;
run;quit;
libname xel clear;

* Keep leading blanks;
* this presevers leading blanks;
%utlfkil(d:/xls/have.xls);
libname xel "d:/xls/have.xls";
libname xel "d:/xls/have.xls";
data xel.have;
  input var1 $char4.;
cards4;
   1
   2
   3
   4
   5
   6
   7
   8
   9
;;;;
run;quit;

libname xel clear;

[djrisks]

Thanks, this worked for me today!

[Patrick]

@djrisks Alternatively use ODS Excel which gives you much more control. ...and it respects SAS formats "out of the box".

data class;
  set sashelp.class;
  format age z10.;
run;

ods excel 
  file='c:\temp\test.xlsx'
  ;
proc print data=class noobs;
run;
ods excel close;

[dhivaghar]

This worked for me thanks.

[Ksharp]

Try add a special STYLE:

data class;
 set sashelp.class;
 _age=put(age,z8. -l);
 drop age;
run;

ods excel  file='c:\temp\test.xlsx' ;
proc report data=class nowd;
define _age/display style={tagattr='type:text format:@'};
run;
ods excel close;

[ChrisNZ]

@Ksharp kludge of the day! 🙂