topic ODS EXCEL DROPPING LEADING ZEROES ON CHARACTER VARIABLE in ODS and Base Reporting

ODS EXCEL DROPPING LEADING ZEROES ON CHARACTER VARIABLE

CLE — Wed, 04 Oct 2017 16:56:46 GMT

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;

Re: ODS EXCEL DROPPING LEADING ZEROES ON CHARACTER VARIABLE

Reeza — Wed, 04 Oct 2017 17:00:56 GMT

Search on here for this issue. Pretty sure it was answered last week.

Re: ODS EXCEL DROPPING LEADING ZEROES ON CHARACTER VARIABLE

CLE — Wed, 04 Oct 2017 17:17:22 GMT

I've searched communities and Google. I cant' find an answer that works.

Re: ODS EXCEL DROPPING LEADING ZEROES ON CHARACTER VARIABLE

Reeza — Wed, 04 Oct 2017 17:28:18 GMT

https://communities.sas.com/t5/ODS-and-Base-Reporting/Exporting-Data-with-Leading-Zeores/m-p/397851/highlight/true#M19400

Re: ODS EXCEL DROPPING LEADING ZEROES ON CHARACTER VARIABLE

CLE — Wed, 04 Oct 2017 17:49:11 GMT

Thank you. I tried this solution and it still drops the leading zeroes.

Re: ODS EXCEL DROPPING LEADING ZEROES ON CHARACTER VARIABLE

Reeza — Wed, 04 Oct 2017 17:56:33 GMT

Post your full sample code please and log.

@CLE wrote:

Thank you. I tried this solution and it still drops the leading zeroes.

Re: ODS EXCEL DROPPING LEADING ZEROES ON CHARACTER VARIABLE

CLE — Thu, 05 Oct 2017 14:10:07 GMT

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:@'};

____

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.

Re: ODS EXCEL DROPPING LEADING ZEROES ON CHARACTER VARIABLE

Reeza — Thu, 05 Oct 2017 15:23:48 GMT

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.

Re: ODS EXCEL DROPPING LEADING ZEROES ON CHARACTER VARIABLE

CLE — Thu, 05 Oct 2017 15:47:12 GMT

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

Re: ODS EXCEL DROPPING LEADING ZEROES ON CHARACTER VARIABLE

ballardw — Thu, 05 Oct 2017 15:48:08 GMT

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

Re: ODS EXCEL DROPPING LEADING ZEROES ON CHARACTER VARIABLE

CLE — Thu, 05 Oct 2017 15:52:43 GMT

Thank you for the reply. I was able to solve with a simple solution Google examples provided.