DATA Step, Macro, Functions and more

Proc export - Retain leading zeroes in Excel

Reply
New User TJP
New User
Posts: 1

Proc export - Retain leading zeroes in Excel

[ Edited ]

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

Super User
Posts: 19,770

Re: Proc export

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

 

Otherwise ODS Excel is your best option. 

Super User
Posts: 10,020

Re: Proc export - Retain leading zeroes in Excel

Add a TAB character before it, then proc export.

 

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

PROC Star
Posts: 7,468

Re: Proc export - Retain leading zeroes in Excel

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

 

Valued Guide
Posts: 505

Re: Proc export - Retain leading zeroes in Excel

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;

Ask a Question
Discussion stats
  • 4 replies
  • 433 views
  • 1 like
  • 5 in conversation