BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
CLE
Obsidian | Level 7 CLE
Obsidian | Level 7

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;
1 ACCEPTED SOLUTION

Accepted Solutions
CLE
Obsidian | Level 7 CLE
Obsidian | Level 7

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

10 REPLIES 10
Reeza
Super User
Search on here for this issue. Pretty sure it was answered last week.
CLE
Obsidian | Level 7 CLE
Obsidian | Level 7

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

CLE
Obsidian | Level 7 CLE
Obsidian | Level 7

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

Reeza
Super User

Post your full sample code please and log.

 


@CLE wrote:

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


 

CLE
Obsidian | Level 7 CLE
Obsidian | Level 7

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.

 

Reeza
Super User

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

CLE
Obsidian | Level 7 CLE
Obsidian | Level 7

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

ballardw
Super User

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

 

CLE
Obsidian | Level 7 CLE
Obsidian | Level 7
Thank you for the reply. I was able to solve with a simple solution Google examples provided.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 7669 views
  • 1 like
  • 3 in conversation