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?
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;
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:
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.