SAS EG output report issue

[AshokD]

Hi All,

I have a dataset final with around 40k records and I am trying to export it to xls with the below logic , it got exported and xls looks good as well but I didn't get SAS EG report because it is trying to print so run time error is coming and no SAS EG report is generated , finally my SAS EG session is frozen. Is there any way to overcome this issue ? Please help

/*Export the file with the required fields*/
ods results off;
ods listing close;
ods tagsets.excelxp file="Batch.xls" style=sansprinter;

ods tagsets.excelxp options(sheet_name = 'Sheet1' frozen_headers = '1');

proc print data= final noobs;
var Account_Number / style={TAGATTR='format:text'};
var CITY / style={TAGATTR='format:text'};
var CODE / style={TAGATTR='format:text'};
var COUNTRY / style={TAGATTR='format:text'};
var CUSTOMER_AGE / style={TAGATTR='format:text'};
var GENDER / style={TAGATTR='format:text'};
var LASTNAME / style={TAGATTR='format:text'};
var NAMETITLE / style={TAGATTR='format:text'};
var FIRSTNAME/ style={TAGATTR='format:text'};
var NAME_SUFFIX / style={TAGATTR='format:text'};
var POSTCODE / style={TAGATTR='format:text'};
var SUFFIX / style={TAGATTR='format:text'};
var mobile_phone / style={TAGATTR='format:text'};
var DOB / style={TAGATTR='format:text'};

run;

ods tagsets.excelxp close;
ods listing;
ods results on;

[SASKiwi · Accepted solution]

I suggest you try - ODS _ALL_ CLOSE; - at the start of your program. That should prevent any report output and only write to the Excel file. Also you may find using ODS EXCEL rather than EXCELXP works better as long as you are on a recent SAS version like 9.4M3 or later. 

[Patrick]

@AshokD 

When running your code on a recent SAS version under RHEL using EG8.2 the code executes without errors but the Excel file created is somehow corrupted and I can't open it with a recent Excel version. 

May be your EG is configured to something which tries to open this Excel automatically and that's when things go wrong.

I still get the error when using .xlsx and when defining a fully valid output path.

If you're on SAS 9.4 then try using the more modern ODS EXCEL destination.

Below code sample should work for you. Just change  %let out_path=~; to a path valid in your environment. The currently used ~ is a shortcut for a Unix home directory.

ods _all_ close;

data class;
  set sashelp.class;
  format age z6.;
run;

%let out_path=~;
ods excel 
  file="&out_path/Batch.xlsx" 
  style=sansprinter
  options(
    start_at="1,1"
    frozen_headers="1"
    frozen_rowheaders="1"
    autofilter="all"
    sheet_name="Class"
    formulas="off"
    /*row_repeat="2"*/
    /*embedded_titles="yes"*/
    )
  ;

proc print data=class style(data)={TAGATTR='type:String'} noobs;
  var name sex age Height;
run;

ods excel close;
ods listing;

[AshokD]

It's working fine now. Thanks