ODS tagsets.ExcelXP

[Nihal]

Hello,

I'm using SAS EG under Unix.

I have to create a report using a sas table. When the table has 0 observations, the program should write a small note and stops.

But if the table contains rows, we should write a report. The program runs without errors if we dont use the ods to send the report.

But, I'm not able to use the ods tagsets.ExcelXP  with data _null_.

Here is the program for this sheet 

ods tagsets.ExcelXP path=pathunix file="Rapports_Hebdo_&date_execut..xls" style=templat options(embedded_titles='yes');
ods tagsets.ExcelXP options (sheet_name="Rapport_ Hebdo2);
 
data _null_;
  set out.sess_term_&date_execut.;
   nombre=symgetn('&nbrobs')  
  if Nombre = 0 then do  ;
      put "#  there is no rows#";
  STOP; END;
  else do;
    call execute('%report'); /a macro that executes a proc sql to select columns*/
  end;
run;
ods tagsets.ExcelXP close;
ods listing;

Can anyone provide me with a solution. it's a bit urgent.

Thank you,

Nihal

[Reeza]

A PUT statement will not write to an ODS destination such as HTML, PDF or TAGSETS. However, there is PROC ODSTEXT if you're using a newer version that will likely do what you need.

[andreas_lds]

Depending on the SAS version you are using, you should switch to ods excel. You need at least SAS 9.4m3. With ods excel the ods text statement can be used to write text to a xlsx-file:

data _null_;
   if &nbrobs. = 0 then do;
      call execute('ods text="# There are no rows#";');
   end;
   else do;
      call execute('%report');
   end;
run;

This does not work with tagset.excelxp!

[Nihal]

Hi, 

Thank you for your answers.

I've tried to use ods excel I got the message below

ERROR: Unable to load module 'SpreadsheetML' from template store!
ERROR: No body file. EXCEL output will not be created.

For ODSTXT I don't know how to use it. As I said in my first message I should send an excel report that contains multiple sheet.

Can someone give me an  example ?

Thanks,

[Nihal]

Sorry I't about "PROC ODSTEXT".

[Reeza]

Lets start with, what version of SAS are you using? You can check using PROC PRODUCT_STATUS; RUN;

PROC ODSTEXT and ODS EXCEL are relatively new (9.4 TS1m3+)

Also, please clarify your question in detail. Your initial question is about sending an email and ods and in the last message you said something new about multi sheet excel. If you can, mock up what you have using a dataset from sashelp so we can run/test things.

[Nihal]

Custom version information: 9.3_M2

The question is that i have to create a excel report then send it by email . I'm working in SAS EG /unix.

The report contains 7 sheets. I've completed 6 sheets but having problem with the last sheet which consists in executing frequencies (%macro rapport).

But if the data in the "set statement" doesn't contains rows, I've tu add note with %put.

Thanks

[Reeza]

Try something like this:

https://documentation.sas.com/?docsetId=mcrolref&docsetTarget=p011imau3tm4jen1us2a45cyenz9.htm&docse...

It gets you fairly close but I'll leave the modifications up to you. 

%macro drive(dsn);
%let dsid=%sysfunc(open(&dsn));
    
 %if &dsid ne 0 %then %do;
  %let cnt=%sysfunc(attrn(&dsid,nlobs));
  %let rc=%sysfunc(close(&dsid));
   %if &cnt ne 0 %then %do;
    proc print data=&dsn;
     title "This is data from data set &dsn"; 
    run;
   %end;
 %end;
 %else %do;
     data missing;
     Error_Message = "Data set &dsn is empty.";
    run;
    
    title;
    proc print data=missing label;
    label error_message = '';
    run;
    

    proc sql noprint;
         drop table missing;
    quit;
    
    %end;;
   
%mend drive;

%drive(sashelp.class);
%drive(demo);