Hi. I'm using ODS EXCEL to produce a proc report in Excel. It works great except for dropping the leading zeroes of a character column (formatted character 7 in work table). I know how to fix it with a tagsets.xp, but it doesn't work for ODS Excel. How can I stop that from happening? Any help is appreciated. See code. Example: 0018362 shows up as 18362. The source data is stored as character fields.
ods _all_ close;
data _null_;
rc= appsrv_header ('Content-type', 'application/vnd.ms-excel');
rc= appsrv_header ('Content-disposition','attachment;filename="results.xlsx"');
run;
ods excel file=_webout
options (sheet_interval='none'
embedded_titles='yes'
sheet_name='Parts Details'
start_at='1,2'
gridlines='on'
orientation='landscape'
tab_color='purple')
;
title justify=center font="Arial" height=14pt color=black bold ‘Parts Cost by Work Order';
title2 justify=center font="Arial" height=10pt color=black 'Data sorted by Total Cost';
proc report data=work.partssummary nowd headline headskip spacing=1
style(header)={font_face="Arial" font_size=13pt bordercolor=black background=orange foreground=white}
style(column) = {background=white font_face="Arial" font_size=10pt};
columns (CC WORK_ORDER_NBR New_Usage_CPN USAGE_MPN REC_CODE TRACKING_FLAG NOUN DESCRIPTION USAGE_QUANTITY AVER_PRICE AvgP_Q_NONMATCH);
define CC / 'Parent CPN' center display style(column)=[cellwidth=85];
define WORK_ORDER_NBR / 'Work Order Nbr' center display;
define New_usage_cpn / 'Child CPN' center display style(column)=[cellwidth=85];
define Usage_mpn / 'Child MPN' center display ;
define REC_CODE / 'Resource Code' center display style(column)=[cellwidth=125];
define TRACKING_FLAG / 'Tracking Level' center display style(column)=[cellwidth=125];
define NOUN / 'Noun' center display style(column)=[cellwidth=125];
define Description / 'Description' center display style(column)=[cellwidth=200];
define Usage_quantity / 'Quantity' center display;
define Aver_Price / 'Average Unit / Price (CPN)' right format=yaa. display;
define AvgP_Q_NONMATCH / 'Total Cost' right analysis sum;
rbreak after / dol dul skip summarize ;
run;
quit;
ods excel close;
I solved it. I added the line "format New_Usage_CPN $CHAR7.;" after the style(column) line. Excel now keeps the leading zeros for the character field.
I had edited the code to conceal the company information. I may have removed the extra comma be accident. Sorry.
Working code (partial):
title2 justify=center font="Arial" height=10pt color=black 'Data sorted by Total Cost';
proc report data=work.partssummary nowd headline headskip spacing=1
style(header)={font_face="Arial" font_size=13pt bordercolor=black background=orange foreground=white}
style(column) = {background=white font_face="Arial" font_size=10pt};
format New_Usage_CPN $CHAR7.;
columns (CC WORK_ORDER_NBR New_Usage_CPN USAGE_MPN REC_CODE TRACKING_FLAG ATA NOUN DESCRIPTION USAGE_QUANTITY AVER_PRICE AvgP_Q_NONMATCH);
define CC / 'Parent CPN' center display style(column)=[cellwidth=85];
I've searched communities and Google. I cant' find an answer that works.
Thank you. I tried this solution and it still drops the leading zeroes.
Post your full sample code please and log.
@CLE wrote:
Thank you. I tried this solution and it still drops the leading zeroes.
Here is the log that has errors. It is working off a work table.
62 define WORK_ORDER_NBR / 'Work Order Nbr' center display;
63 define New_usage_cpn / style={tagattr='WRAP:YES'} style(data)={tagattr='format:@'};
63 define New_usage_cpn / style={tagattr='WRAP:YES'} style(data)={tagattr='format:@'};
____
22
ERROR 22-322: Syntax error, expecting one of the following: ), CALLDEF, COLUMN, HDR, HEADER, LINES, REPORT, SUMMARY.
63 define New_usage_cpn / style={tagattr='WRAP:YES'} style(data)={tagattr='format:@'};
____
202
ERROR 202-322: The option or parameter is not recognized and will be ignored.
So it's not 'not working' because the solution doesn't work, it's not working because your code has errors.
This is why you need to include the log.
I'm not sure what the error is, but you need to post your full code and log. From the initial post you have an incorrect comma, which is what causes the colours to be messed up on the forum and in SAS. So you have syntax errors unrelated to this issue at all.
Fix those first and then come back to this issue.
This works fine for me in SAS 9.4 TS1M3. Note that ODS EXCEL was not production until SAS 9.4 TS1M3.
I solved it. I added the line "format New_Usage_CPN $CHAR7.;" after the style(column) line. Excel now keeps the leading zeros for the character field.
I had edited the code to conceal the company information. I may have removed the extra comma be accident. Sorry.
Working code (partial):
title2 justify=center font="Arial" height=10pt color=black 'Data sorted by Total Cost';
proc report data=work.partssummary nowd headline headskip spacing=1
style(header)={font_face="Arial" font_size=13pt bordercolor=black background=orange foreground=white}
style(column) = {background=white font_face="Arial" font_size=10pt};
format New_Usage_CPN $CHAR7.;
columns (CC WORK_ORDER_NBR New_Usage_CPN USAGE_MPN REC_CODE TRACKING_FLAG ATA NOUN DESCRIPTION USAGE_QUANTITY AVER_PRICE AvgP_Q_NONMATCH);
define CC / 'Parent CPN' center display style(column)=[cellwidth=85];
@CLE wrote:
Here is the log that has errors. It is working off a work table.
62 define WORK_ORDER_NBR / 'Work Order Nbr' center display;
63 define New_usage_cpn / style={tagattr='WRAP:YES'} style(data)={tagattr='format:@'};
63 define New_usage_cpn / style={tagattr='WRAP:YES'} style(data)={tagattr='format:@'};
____
22
ERROR 22-322: Syntax error, expecting one of the following: ), CALLDEF, COLUMN, HDR, HEADER, LINES, REPORT, SUMMARY.
63 define New_usage_cpn / style={tagattr='WRAP:YES'} style(data)={tagattr='format:@'};
____
202
ERROR 202-322: The option or parameter is not recognized and will be ignored.
Post code and messages from the log into a code box opened with the forum {I} menu icon to prevent the forum message box from reformatting the text. The log the underscore characters would be under the offending location but note that they have likely been moved into the first column.
Also you need to provide the entire procedure step as quite often an error is caused by something earlier in the code but not caught until a later line. This is especially true for the errors caused by mismatched parentheses or quotation marks.
The bit expecting one of the following: ) is an very likely indicator that there is a mismatched ( prior somewhere.
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.