BookmarkSubscribeRSS Feed
TJP
Calcite | Level 5 TJP
Calcite | Level 5

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

9 REPLIES 9
Reeza
Super User

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
Super User

Add a TAB character before it, then proc export.

 

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

art297
Opal | Level 21

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
Barite | Level 11
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
Barite | Level 11
Thanks, this worked for me today!
Patrick
Opal | Level 21

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

Patrick_0-1634173006334.png

 

dhivaghar
Calcite | Level 5

This worked for me thanks.

Ksharp
Super User

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;

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 9 replies
  • 7926 views
  • 12 likes
  • 9 in conversation