DATA Step, Macro, Functions and more

loosing leading zeros during proc export

Reply
Regular Contributor
Posts: 178

loosing leading zeros during proc export

data have;

format app_date mmddyy10.;

date_mth = month(app_date);

date_mth1 = put(date_mth,Z2.);

run:

 

output
ID    app_date   date_mth
12    12/09/2018  12

65    06/15/2018  06

This works ok in the datastep

 

proc export outfile = "\\myserver\have.xlsb"

data= have

label

DBMS = excelcs replace;

Sheet = "Data";

Server = "myserver"

 

Output
LOAN_ID    APPLICATION DATE   DATE MONTH

 

run;

output example
ID     app_date      date_mth

12    12/9/2018       12

65    6/15/2018       6

 

The exported output correctly eliminates the leading zeroes in the app_date however I want to retain the leading zero in date_mth just as I did in the datastep using the put statement. So 6 should be 06 however it truncates during export.  How do I get it back?

Super User
Super User
Posts: 7,860

Re: loosing leading zeros during proc export

If you want to tell Excel how to format the data you probably need to use ODS EXCEL instead of PROC EXPORT.

Try this program.

data have ;
  input id app_date char_mth :$2. ;
  informat app_date mmddyy.;
  format app_date yymmdd10. ;
  date_mth = month(app_date);
cards;
12    12/09/2018  12
65    06/15/2018  06
;

proc export data=have 
  outfile='c:\downloads\test1.xlsx' replace
  dbms=xlsx
;
run;

ods excel file='c:\downloads\test2.xlsx' ;
proc print data=have ;
 var id ;
 var app_date / style(data)={tagattr='type:Date format:mm/dd/yyyy'}; 
 var char_mth / style(data)={tagattr='type:text'};
 var date_mth / style(data)={tagattr='format:00'} ;
run;
ods excel close;

image.png

Super User
Posts: 10,618

Re: loosing leading zeros during proc export

Or adding a special character before it.

 

data have;

format app_date mmddyy10.;

date_mth = month(app_date);

date_mth1 = cats('09'x,put(date_mth,Z2.));

run:

Ask a Question
Discussion stats
  • 2 replies
  • 98 views
  • 0 likes
  • 3 in conversation