The SAS Output Delivery System and reporting techniques

ODS EXCEL DROPPING LEADING ZEROES ON CHARACTER VARIABLE

Accepted Solution Solved
Reply
Highlighted
Contributor CLE
Contributor
Posts: 21
Accepted Solution

ODS EXCEL DROPPING LEADING ZEROES ON CHARACTER VARIABLE

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;

Accepted Solutions
Solution
2 weeks ago
Contributor CLE
Contributor
Posts: 21

Re: ODS EXCEL DROPPING LEADING ZEROES ON CHARACTER VARIABLE

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

View solution in original post


All Replies
Super User
Posts: 19,115

Re: ODS EXCEL DROPPING LEADING ZEROES ON CHARACTER VARIABLE

Search on here for this issue. Pretty sure it was answered last week.
Contributor CLE
Contributor
Posts: 21

Re: ODS EXCEL DROPPING LEADING ZEROES ON CHARACTER VARIABLE

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

Super User
Posts: 19,115

Re: ODS EXCEL DROPPING LEADING ZEROES ON CHARACTER VARIABLE

Contributor CLE
Contributor
Posts: 21

Re: ODS EXCEL DROPPING LEADING ZEROES ON CHARACTER VARIABLE

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

Super User
Posts: 19,115

Re: ODS EXCEL DROPPING LEADING ZEROES ON CHARACTER VARIABLE

Post your full sample code please and log.

 


CLE wrote:

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


 

Contributor CLE
Contributor
Posts: 21

Re: ODS EXCEL DROPPING LEADING ZEROES ON CHARACTER VARIABLE

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.

 

Super User
Posts: 19,115

Re: ODS EXCEL DROPPING LEADING ZEROES ON CHARACTER VARIABLE

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.

 

 

delete_leading_zeroes.JPG

Solution
2 weeks ago
Contributor CLE
Contributor
Posts: 21

Re: ODS EXCEL DROPPING LEADING ZEROES ON CHARACTER VARIABLE

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

Super User
Posts: 11,124

Re: ODS EXCEL DROPPING LEADING ZEROES ON CHARACTER VARIABLE


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.

 

Contributor CLE
Contributor
Posts: 21

Re: ODS EXCEL DROPPING LEADING ZEROES ON CHARACTER VARIABLE

Thank you for the reply. I was able to solve with a simple solution Google examples provided.
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 259 views
  • 0 likes
  • 3 in conversation