topic Re: loosing leading zeros during proc export in SAS Programming

loosing leading zeros during proc export

Q1983 — Sat, 31 Mar 2018 15:46:49 GMT

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?

Re: loosing leading zeros during proc export

Tom — Sat, 31 Mar 2018 17:04:38 GMT

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;

Re: loosing leading zeros during proc export

Ksharp — Sun, 01 Apr 2018 11:18:53 GMT

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: