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
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!
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,
Sorry I't about "PROC ODSTEXT".
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
Try something like this:
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);
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.