I created a table to store the instructions and am reading it in with ODS to output into my excel report that I produce. I am getting this error when I execute using Unix.
PROC REPORT DATA=WORK.INSTRUCTIONS LS=250 PS=60 SPLIT="/" NOCENTER ;
COLUMN ( unmapped_category note description action );
DEFINE unmapped_category / DISPLAY FORMAT= $CHAR23. WIDTH=23 SPACING=2 LEFT "unmapped_category" ;
DEFINE note / DISPLAY FORMAT= $CHAR19. WIDTH=19 SPACING=2 LEFT "note" ;
DEFINE description / DISPLAY FORMAT= $CHAR377. WIDTH=377 SPACING=2 LEFT "description" ;
DEFINE action / DISPLAY FORMAT= $CHAR293. WIDTH=293 SPACING=2 LEFT "action" ;
RUN;
ERROR: The width of description is not between 1 and 250. Adjust the column width or line size.
NOTE: This affects LISTING output.
How do I increase the length in my proc report statement to avoid this error?
A few months ago I asked the question about how I could create the sheet of instuctions and really never got an answer except to put into a static table and read it in.
Any assistance would be greatly appreciated.
Thank you,
Elliott
Hi:
As the NOTE tells you, the ERROR message ONLY impacts the LISTING output -- it will not have an impact on ODS RTF, ODS PDF or ODS EXCEL. LS=250 will only impact the LISTING window. So when you are using PROC REPORT and creating output for EXCEL, I assume you are using an ODS destination other than the LISTING window.
Basically, you can ignore this message IF you have other ODS statements in your code. Many of your PROC REPORT options, however, such as WIDTH= and SPACING= lead me to think that you are ONLY using LISTING and NOT ODS HTML, ODS RTF, ODS PDF or ODS EXCEL. If you are using the LISTING window, then WIDTH=377 will be considered invalid (as will your other widths) because the max LINESIZE allowed is 256 and all your widths together (in LISTING) must add up to 256 or less.
Are you using ODS? Can you show your ODS destination statements? How are you getting your output into Excel.
You could try putting this:
ODS LISTING CLOSE;
ODS msoffice2k file='/use/bin/somedir/myinstructions.html';
**...proc report code...;
ODS msoffice2k close;
and then open the resulting HTML file with Excel to see whether that works better.
Otherwise, if you really are using LISTING destination, then your current approach and widths violates the LINESIZE rules for LISTING output.
cynthia
Hi:
In the absence of data or any idea that you were emailing, here is the code that I mocked up to show you creating instructions. My tendency would be to email a PDF file and not an Excel file, but I am showing the example that I mocked up. The email part would be mostly the same as what you show. Instead of octet-stream as the content-type for email, I usually use a different content type for opening the file at the other end: application/vnd.ms-excel .
Also, when you use TAGSETS.EXCELXP, I recommend against using .XLS as the file extension. The actual correct file extension is .XML -- if you use .XLS, then versions of Excel after 2007, will display a "warning" message about how the contents of the file do not match the file extension and typically, users can get upset about this message. If you use the correct file extension of .XML, because TAGSETS.EXCELXP is creating a Spreadsheet Markup Language XML file, then Excel will not complain when it opens the file.
But, some mail systems prohibit sending or receiving XML files, which is why I find it safer to email RTF or PDF files. But, I've included examples of all different ODS destinations in the sample code below.
Here's my code (including the part that makes the work.instructions file):
data work.instructions;
length unmapped_category $23 note $25 description $400 action $300;
set sashelp.class;
where age in (15,16);
unmapped_category=catx('~','Category',sex,name);
note=catx(' ','This person is',age,'years');
if sex = 'F' then description=catx(' ','Twas brillig and the slithy toves',
'Did gyre and gimble in the wabe. All mimsy were the borogroves',
'And the momeraths outgrabe. Beware the Jabberwock my son!',
'The jaws the bite, the claws that catch.',
'Beware the Jubjub bird and shun the frumious Bandersnatch.',
'He took his vorpal sword in hand.',
'Long time the manxome foe he sought.',
'So rested he by the Tumtum tree',
'And stood awhile in thought.');
else if sex = 'M' then description=catx(' ','Lorem ipsum dolor sit amet, consectetur adipiscing elit.',
'Morbi justo ex, dapibus volutpat vel, eleifend eget ante.',
'Maecenas ac lacus cursus, efficitur enim vel, gravida sem.',
'Lorem id congue mi. Nam pharetra rhoncus.',
'Praesent vitae convallis massa.',
'Morbi porta diam sit amet enim aliquam, a pellentesque nisi consequat.',
'Nulla ut ornare nunc. Mauris dui nibh, commodo vitae gravida metus.' );
action=catx(' ','Possible Actions when in a dangerous situation:',
'You could hope that a superhero swoops in and saves the day.',
'Or, you could be beamed up into the Starship Enterprise and taken out of your situation.',
'Or the zombie apocalypse starts and it nothing is more dangerous than that, so you start running.');
run;
options orientation=landscape;
ods listing close;
ods rtf file='c:\temp\instructions_report.rtf';
ods pdf file='c:\temp\instructions_report.pdf';
ods html file='c:\temp\instructions_report_ht4.html' style=htmlblue;
ods msoffice2k file='c:\temp\instructions_report_mso.html' style=htmlblue;
ods excel file='c:\temp\instructions_report_xlnew.xlsx' style=htmlblue
options(embedded_titles='yes' sheet_name='Instructions');
ods tagsets.excelxp file='c:\temp\instructions_report_xp.xml' style=htmlblue
options(embedded_titles='yes' sheet_name='Instructions' autofit_height='yes');
PROC REPORT DATA=WORK.INSTRUCTIONS SPLIT="/"
style(column)={just=left vjust=top};
title 'Instructions';
COLUMN ( unmapped_category note description action );
DEFINE unmapped_category / DISPLAY "unmapped_category" ;
DEFINE note / DISPLAY "note" ;
DEFINE description / DISPLAY "description" style(column)={width=4in};
DEFINE action / DISPLAY "action" style(column)={width=4in};
RUN;
ods tagsets.excelxp options(embedded_titles='yes' sheet_name='Need_Lessons');
ods excel options(embedded_titles='yes' sheet_name='Need_Lessons');
proc report data=sashelp.class;
where age in (15,16);
title 'Ready for Driving Lessons';
column age sex name height weight;
define age/ order;
define sex / order;
define name / display;
define height / display;
define weight / display;
run;
ods tagsets.excelxp options(embedded_titles='yes' sheet_name='No_Lessons');
ods excel options(embedded_titles='yes' sheet_name='No_Lessons');
proc report data=sashelp.class;
where age not in (15,16);
title 'Not ready for Driving Lessons';
column age sex name height weight;
define age/ order;
define sex / order;
define name / display;
define height / display;
define weight / display;
run;
ods _all_ close;
ods listing;
And here's what the output looks like using TAGSETS.EXCELXP:
And also what the output looks like using ODS EXCEL:
Since the program just uses SASHELP.CLASS, you can run it and adapt the techniques to fit your needs.
cynthia
Hi Cynthia,
Sorry I have not responded before now, I have not had a chance to work on this again until yesterday.
My report provides records of data, that is why it is in excel, we need that format.
When I created the instructions I wrote them in excel then imported to a sas dataset.
My program reads in the data set, then using ODS they output in my report.
I tried changing the format to XML, the report looks great just what I want and emails fine but I still get the same error in the log when I execute at the unix command line. That is how are production code is executed. If the log has errors they will not place my report in production.
I noticed your example wrote out the insturctions within the code. I prefer not to do it that way becasue if I want to update the instructions it would require a production code change. With my static table I can update on the fly and it does not require code changes and testing and handing off the updated program/logs etc to production.
Is there a better way to read in my dataset? Or when I do the import in eg from the excel, are the setting that I should use for that amount of text? So far I have just done a straight import.
I tried to makes sense of your ODS code, and made modifications to mine, but none produced output so I obviously don't know enough about ODS to figure out which parts need to be changed/updated.
Thanks for your help.
Elliott
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.