topic Re: Proc export - Retain leading zeroes in Excel in SAS Programming

Proc export - Retain leading zeroes in Excel

TJP — Thu, 23 Mar 2017 03:28:33 GMT

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

Re: Proc export

Reeza — Thu, 23 Mar 2017 03:27:55 GMT

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

Otherwise ODS Excel is your best option.

Re: Proc export - Retain leading zeroes in Excel

Ksharp — Thu, 23 Mar 2017 03:49:19 GMT

Add a TAB character before it, then proc export.

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

Re: Proc export - Retain leading zeroes in Excel

art297 — Thu, 23 Mar 2017 04:17:23 GMT

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

Re: Proc export - Retain leading zeroes in Excel

rogerjdeangelis — Thu, 23 Mar 2017 14:57:40 GMT

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;

Re: Proc export - Retain leading zeroes in Excel

djrisks — Wed, 13 Oct 2021 21:47:28 GMT

Thanks, this worked for me today!

Re: Proc export - Retain leading zeroes in Excel

ChrisNZ — Thu, 14 Oct 2021 00:34:57 GMT

@Ksharp kludge of the day! 🙂

Re: Proc export - Retain leading zeroes in Excel

Patrick — Thu, 14 Oct 2021 23:54:03 GMT

@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;

Re: Proc export - Retain leading zeroes in Excel

Ksharp — Thu, 14 Oct 2021 11:43:10 GMT

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;

Re: Proc export - Retain leading zeroes in Excel

dhivaghar — Mon, 21 Nov 2022 08:30:32 GMT

This worked for me thanks.