The SAS Output Delivery System and reporting techniques

Creating an Instructions sheet in report output

Posts: 74

Creating an Instructions sheet in report output

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.  


COLUMN  ( unmapped_category note description action );


DEFINE  unmapped_category / DISPLAY FORMAT= $CHAR23. WIDTH=23    SPACING=2   LEFT "unmapped_category" ;


DEFINE  description / DISPLAY FORMAT= $CHAR377. WIDTH=377   SPACING=2   LEFT "description" ;

DEFINE  action / DISPLAY FORMAT= $CHAR293. WIDTH=293   SPACING=2   LEFT "action" ;



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,


Posts: 8,864

Re: Creating an Instructions sheet in report output

[ Edited ]

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


Posts: 74

Re: Creating an Instructions sheet in report output

Posted in reply to Cynthia_sas
This is the code I am using to create the output and email to myself. This will run and produce the correct output if I run manually using SAS EG. But to have it in a production environment it is executed from unix cmd line and it fails do to the error I posted earlier.

So I need to edit the length so that I can produce the output or find another way to create the information sheet in the report.


/** auto-email **/

%let html = /xx/xxx/adhoc_rpts/cita_compare.xls;

%let rptname= cita_compare;

filename rptfl "/xx/xxx/adhoc_rpts/&rptname..xls";

ods path(prepend) work.templat(update);

proc template;

define style styles.XLsansPrinter;

parent = styles.sansPrinter;

style header from header /

font_face = "Arial, Helvetica"

font_size = 10pt

font_weight = bold

font_style = roman

foreground = cx000000

background = #99ccff

just = center

vjust = bottom;


run; quit;

/*ODS won't create a tab for an empty dataset, so if there are no errors, this dummy dataset will add a row stating there are no errors

and use that as a seed file. */

data no_review;

format note $20.;



/*create a macro variable that has the count of error samples*/

proc sql noprint;

select count(*) INTO Smiley Surprisedbsvar

from compare_results;


/*if the error samples count=0 then set an obsno macro variable to 1, else set it to 0*/

data _null_;

if &obsvar.=0 then obsnosamp=1;

else obsnosamp=0;

call symput("obsnosamp",obsnosamp);


/*append the no_review seed dataset to the samples dataset ONLY if the samples dataset is empty

- uses the obsno macro variable to accomplish this

- the nowarn and force options will force the append even though the variable lists don't match and not create log warnings*/

proc append data=no_review(obs=&obsnosamp.) base=compare_results nowarn force;


proc report nofs data=work.compare_results list;

columns _all_;


options validvarname=upcase label=0;

proc report nofs data=work.compare_results out=work.compare_results1 list;

columns _all_;


options validvarname=any label=1;


proc report nofs data=work.instructions list;

columns _all_;


options validvarname=upcase label=0;

proc report nofs data=work.instructions out=work.instructions1 list;

columns _all_;


options validvarname=any label=1;

ods listing close;

ods tagsets.excelxp

file = rptfl style = XLsansPrinter

options(embedded_titles='on' minimize_style='yes' wraptext='no' AUTOFIT_HEIGHT='yes' )


ods noproctitle*/;

/* Instructions Sheet */

ods tagsets.excelxp options(sheet_name="Instructions" sheet_interval='none' embedded_titles='yes' wraptext = 'yes'


proc print data=instructions1 noobs ;

title "Research Instructions";








/*Summary tab*/

ods tagsets.excelxp options(sheet_name = 'Unmapped Cita Compare Report' sheet_interval='table' embedded_titles='yes'wraptext = 'no' autofilter='all'


proc print data = work.compare_results1 Noobs;

title "Unmapped Cita Compare Report";













ods tagsets.excelxp close;

%put &todaydt.;

filename myfile email to = ("")


SUBJECT="Unmapped Cita Compare Report for &todaydt."

attach = ("&html" ct = 'application/octet-stream');

data _null_;

file myfile;

put "Your Unmapped TQM CITA Compare Report for &todaydt. is now available."

/ / " "

/ / "Thank you and have a great day!"

/ / " "

/ /"Sincerely,"

/ /"Elliott Olds"

/ / " "



filename myfile clear;


Posts: 8,864

Re: Creating an Instructions sheet in report output


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


  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);
  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.');

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');

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

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;

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;

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.



Posts: 74

Re: Creating an Instructions sheet in report output

Posted in reply to Cynthia_sas

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.


Posts: 8,864

Re: Creating an Instructions sheet in report output

Without your data or a sample of your data, it is hard to understand what you mean when you say that you get an error when you submit on a UNIX command line. Since I don't have Unix to test with, I wouldn't be able to test your code in Unix anyway, but if you posted data with your code, someone might be able to help you.

Looking at your code, I don't see where you create work.instructions and I don't understand why you keep using OUT= with PROC REPORT. So clearly there is something in your process that I am not understanding clearly.

In this case, I think you need to open a track with Tech Support so they can look at your data, look at all your code and try to replicate your issue on a Unix system.

Ask a Question
Discussion stats
  • 5 replies
  • 2 in conversation