CODEWITHSUNDEEP
sas
Jack_loc
Jack_loc

Reputation: 55

SAS: ODS Excel locks file if there are no data

I noticed that whenever I try to run PROC REPORT in ODS Excel and there is no data, it never finishes writing to the file and just gets stuck. The file remains locked.

Why is that and how to fix it? I don't know beforehand if my report will have any data.

Here is example code:

    data person;
   input name $ dept $;
   datalines;

;


GOPTIONS RESET=ALL;
 ODS Excel FILE = "C:\Users\jloktie\Desktop\test1.xlsx" STYLE = CSROD

options(embedded_titles='yes' sheet_interval='none' autofilter= 'all' 
Absolute_Column_Width = '10') ;

proc report data = person nowd  HEADLINE HEADSKIP

style (report) = {background = white font_face = "Verdana" font_size = 8pt  just=LEFT }
style (column) = {background = white font_face = "Verdana" font_size = 8pt  just=LEFT}
style (header) = {foreground = white font_face="Verdana" font_size = 10pt just=LEFT background = cx4d4d4d}  ;


columns name;

define name / "Name";

run;

ODS _ALL_ CLOSE;

Here is what the log says:

29349  data person;
29350     input name $ dept $;
29351     datalines;

NOTE: SAS went to a new line when INPUT statement reached past the end of a line.
NOTE: The data set WORK.PERSON has 0 observations and 2 variables.
NOTE: DATA statement used (Total process time):
      real time           0.03 seconds
      cpu time            0.01 seconds


29353  ;
29354
29355
29356  GOPTIONS RESET=ALL;
29357   ODS Excel FILE = "C:\Users\jloktie\Desktop\test1.xlsx" STYLE = CSROD
29358
29359  options(embedded_titles='yes' sheet_interval='none' autofilter= 'all'
29360  Absolute_Column_Width = '10') ;
29361
29362  proc report data = person nowd  HEADLINE HEADSKIP
29363
29364  style (report) = {background = white font_face = "Verdana" font_size = 8pt  just=LEFT }
29365  style (column) = {background = white font_face = "Verdana" font_size = 8pt  just=LEFT}
29366  style (header) = {foreground = white font_face="Verdana" font_size = 10pt just=LEFT background
29366! = cx4d4d4d}  ;
29367
29368
29369  columns name;
29370
29371  define name / "Name";
29372
29373  run;

NOTE: No observations in data set WORK.PERSON.
NOTE: No observations in input data set.
NOTE: PROCEDURE REPORT used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


29374
29375  ODS _ALL_ CLOSE;
NOTE: Writing EXCEL file: C:\Users\jloktie\Desktop\test1.xlsx

I'm using SAS 9.4 TS Level 1M4.

Upvotes: 0

Views: 893

Answers (2)

Richard
Richard

Reputation: 27508

In M6, ODS EXCEL will create an empty workbook and open it.

Subsequent submission of the same code, or any code that attempts to recreate the workbook, will find the destination locked if you do not close the empty workbook first.

ODS Excel 
    FILE = "test1.xlsx" 
    options ( 
        embedded_titles='yes' 
        sheet_interval='none' 
        autofilter= 'all'
        Absolute_Column_Width = '10'
    ) 
;

* no output;
proc report data=sashelp.class;
where name=:'z';
run;

* closing the destination creates an empty workbook;
ods excel close;

Upvotes: 0

Tom
Tom

Reputation: 51621

ODS EXCEL does not produce a valid XLSX file if you do not provide it any outputs. So either add some other output or skip running the ODS statements when there is no output.

One way to always have output is to insure the dataset always has at least one observation.

data for_report ;
  if _n_=1 and eof then output;
  set person end=eof;
  output;
end;
proc report data=for_report ....

Upvotes: 1

Related Questions