Change the reprot from printer to excel

[pbad]

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;

[RW9]

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.

[Cynthia_sas]

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

[pbad]

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 🙂

[pbad]

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. 

[SuryaKiran]

Try using ODS tagsets.ExcelXP with ABSOLUTE_COLUMN_WIDTH. 

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

[Cynthia_sas · Accepted solution]

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;

[pbad]

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;                                                           

[Cynthia_sas]

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