topic Re: Change the reprot from printer to excel in ODS and Base Reporting

Change the reprot from printer to excel

pbad — Fri, 20 Apr 2018 09:55:46 GMT

Hi Team,

I appreciate to get the answers from you.

At the moment my SAS job is send the reports to printer. Clients wants me to generate the excel report instead.
I am printing the specific keywords at different column positions.

The biggest problem is to keep headers/keywords at different column positions. I have tried different SAS
procedures but not getting the right headers e.g. ODS TAGSETS.EXCELXP/Proc teamplate/proc print/proc tablulate.

The existing code can be used to get the notepad easily but I am not getting excel output as expected.
Any advise will be appreciated.

Thanks

Existing code for your reference:-

OPTIONS NODATE nocenter ls=90 ps=66 pageno=1;
filename pri sysout=x dest=esf fcb='abcd' pgm=&printer new copies=1;

data xx;
set yy end=**bleep**;

file abc header=H notitles;
X=repeat('_',88);
Y=repeat('_',88);

PUT @2 var1 commax14.2 @18 var2 commax14.2 @34 var3 @46 var4
@58 var5 @84 var6 6.0;
put @1' ';

varx+var1;
vary+var2;

If **bleep**=1 then do;
put @1 X;
put @1' ';
PUT @2 varx commax14.2 @18 var2 commax14.2 @34 var3
@58 var5 @80 cn ;
varx=0;
vary=0;
end;

return;

h:
nrside+1;
ds=today();
kl=timepart(datetime());
put @5'xxxxxxxxxxxx '   @37 yyy          @81'Side' @86 nrside 2.;
put                     @64'printet d.'@74 ds eurdfdd. @83 kl hhmm.;
put @5'- pppppppppppp'            @37 'aaaaa=' @44 bbbbbb 6.;
put @1 X;
put @2 '     test' @19' Check test' @39 'abc' @46'ghg'
    @58'xx' @84'SAS nr';
put @1' ';
return;
run;

Re: Change the reprot from printer to excel

RW9 — Fri, 20 Apr 2018 10:28:11 GMT

You can't generate an Excel report like that. Excel is a file type, and you need to conform to the file type to be able to write data. The text file you are currently writing to is a plain text file, and you are manipulating the pointer to put text in various positions. This is really very old school way of doing things.

Excel files are not plain text files, they are (well apart from being ZIPs with various XML files) grids of data, i.e. spreadsheets. So this is very different way of thinking about how to get data out to it. As I can't see what the output should look like, nor have any test data in the form of a datastep, is very hard to point you in the direction, but one way to mimic the output, would be to have a dataset which variables, and put the various text strings in there where you want to see them, e.g.:
VAR1 VAR2 VAR3

Something

Page 1 of 2

...

Then use ods excel or excelxp to report that dataset out exactly as it is.

I would point out that Excel is a really poor format for any purpose, but especially for data transfer if that is your purpose.

Re: Change the reprot from printer to excel

SuryaKiran — Fri, 20 Apr 2018 12:39:52 GMT

Try using ODS tagsets.ExcelXP with ABSOLUTE_COLUMN_WIDTH.

http://support.sas.com/resources/papers/proceedings14/1881-2014.pdf

Re: Change the reprot from printer to excel

Cynthia_sas — Fri, 20 Apr 2018 13:54:00 GMT

Hi:
Are you creating a report for the mainframe? Typically the FILENAME and SYSOUT are the type of syntax you see for mainframe reports. In this case, your DATA step code should work to produce a free format type report for the mainframe printer or sysout, but as already noted by others, ODS EXCEL is NOT a printer or sysout destination. So this code will NOT work with ODS destinations. If you don't understand why this won't work then you need to research ODS and understand what you CAN do with ODS because you are going to need to explain to your client that this report will likely need to be redesigned to make the transition from the current format to an Excel-based format.

If you need more help, you need to post some sample data and possibly a picture of the output you have and then a mock-up in Excel of the output you envision being able to produce. Just making your mock-up in Excel may illustrate for you some of the issues you'll face...for example, a header that repeats at the top of the page when you print an excel sheet does not show inside the Workbook because you create it in a different way. And spacing in Excel isn't done by "print position" but instead is done by filling rows and columns.

Cynthia

Re: Change the reprot from printer to excel

pbad — Tue, 24 Apr 2018 05:03:19 GMT

Thanks,

You assumption is absolutely correct 🙂 The report was created many years back when there were very few SAS solutions. Existing report is getting created on Mainframe and it is created for getting the prints on printer. I will share the sample report, however I have created the notepad file and trying to get the excel sheet.

Re: Change the reprot from printer to excel

pbad — Tue, 24 Apr 2018 06:39:24 GMT

pls see the attachment (sample data). This is in notepad.

Yes, the program is in Mainframe. Only problem is header position to get it, trying to get it using different SAS procedures 🙂

Re: Change the reprot from printer to excel

Cynthia_sas — Tue, 24 Apr 2018 15:19:16 GMT

Hi:
Both ODS TAGSETS.EXCELXP and ODS EXCEL support the PRINT_HEADER and PRINT_FOOTER suboptions. Please look at pages 6 and 7 of this paper https://www.lexjansen.com/nesug/nesug08/ap/ap06.pdf for an example of using PRINT_FOOTER. An example of PRINT_HEADER can be found in the code below. But to see the headers you have to go into PRINT Preview inside Excel.

Cynthia

Here's the code:

ods excel file='c:\temp\make_header_xl.xlsx'
          options(absolute_column_width="8, 8, 5, 8, 20"
                  embedded_titles='yes'
                  print_header='&L &D &C Report for Class &R Page &P');
ods tagsets.excelxp file='c:\temp\make_header_xp.xml' style=htmlblue
                    options(absolute_column_width="8, 8, 5, 8, 20"
                            embedded_titles='yes'
                            print_header='&L &D &C Report for Class &R Page &P');

proc report data=sashelp.class nowindows split='*';
  column  name sex age height weight;
  define  name   / display   'Student*Name'     style={just=l};
  define  sex    / display   '*Gender'          style={just=r};
  define  age    / display   '*Age'             style={just=c};
  define  height / display   'Height*(inches)'  style={just=c};
  define  weight / display   'Weight*(pounds)'  style={just=c};
  title 'Draft & Confidential';
run;

ods tagsets.excelxp close;
ods excel close;

Re: Change the reprot from printer to excel

pbad — Fri, 20 Jul 2018 09:24:48 GMT

Dear all Thanks for your response. I have used the combination of tagsets.excelXP and proc report to solve it.

OPTIONS NODATE center;
title; footnote;
ods listing close;
ods escapechar="^";
ods tagsets.ExcelXP
     file="/tmp/&sysuid. abc.xlsx"
options (Embedded_Titles = 'yes'
         Embedded_footnotes = 'yes'
         sheet_name="&header");

proc report data=pppp Headline Headskip Split='*'
     style(report) = [frame       = box
                        borderwidth = 2 ]
      style(header) = [font_weight = bold
                        font_size   = 2.0]
      style(column) = [font_weight = medium
                        background = beige
                        font_size   = 2.0]
      ;
      column aaaa bbbb cccc;
define aaaa /display analysis sum 'dddd' format=numx14.2;
define bbbb /display 'ttttt' style(column)={tagattr='0000000000'};
define cccc      /display 'Bogf. ref.' ;
title1 JUSTIFY=LEFT "&Header";
title2 JUSTIFY=RIGHT "printet d.&ds&kl";

RBREAK AFTER /SUMMARIZE;
   COMPUTE after;
            type = 'dddd';
      endcomp;
run;
ODS TAGSETS.EXCELXP CLOSE;
ods _all_ close;
ods listing;
filename mail email
subject="The &header file for your reference"
to     = abcd
from   = abcd
attach =( "/tmp/&sysuid. pay.xlsx"
           ext="xls"
           name="&header");
run;

data _null_;
file mail;
put "Automatic email for &header sent from SAS job";
run;

Re: Change the reprot from printer to excel

Cynthia_sas — Fri, 20 Jul 2018 14:43:41 GMT

Hi:
I hope this is working for you. In the past, naming output from TAGSETS.EXCELXP with an XLSX extension could cause a Microsoft error message. If you want to create an XLSX file, you should use ODS EXCEL, NOT TAGSETS.EXCELXP.

Cynthia