Hello
I want that SAS will print a message if the data set is null
What is wrong with this code that I get error
data use_this_if_no_obs;
msg = 'No Data';
run;
data tbl_null;
if 0;
run;
%let ds = %sysfunc(ifc(%nobs(iDs=tbl_null) eq 0,
use_this_if_no_obs,
tbl_null));
proc print data=&ds;
run;
data use_this_if_no_obs; msg='No Data'; run; data _null_; set sashelp.vtable (where=(libname="WORK" and memname="ABC")); if nobs=0 then call execute('proc print data=use_this_if_no_obs; run;'); else call execute('proc print data=abc; run;'); run;
This will print use_this_if_no_obs is there are no observations in WORK.ABC. Otherwise it will print WORK.ABC.
What error do you get? Show us the SASLOG.
WARNING: Apparent invocation of macro NOBS not resolved.
WARNING: Apparent invocation of macro NOBS not resolved.
ERROR: Required operator not found in expression: %nobs(iDs=tbl_null) eq 0
ERROR: Argument 1 to function IFC referenced by the %SYSFUNC or %QSYSFUNC macro function is not a number.
ERROR: Invalid arguments detected in %SYSCALL, %SYSFUNC, or %QSYSFUNC argument list. Execution of %SYSCALL statement or %SYSFUNC
or %QSYSFUNC function reference is terminated.
21052 %let ds = %sysfunc(ifc(%nobs(iDs=tbl_null) eq 0,
21053 use_this_if_no_obs,
21054 tbl_null));
21055 proc print data=&ds;
-
22
ERROR 22-322: Expecting a name.
21056 run;
ERROR: File WORK.NAME.DATA does not exist.
%nobs is no standard macro:
35 %let ds = %sysfunc(ifc(%nobs(iDs=tbl_null) eq 0, WARNING: Apparent invocation of macro NOBS not resolved. 36 use_this_if_no_obs, 37 tbl_null)); WARNING: Apparent invocation of macro NOBS not resolved.
To use it, you need to define it.
How can I do it please?
Should I add one more %let statement?
You have to either create a macro named %NOBS, or (hint hint hint) find it on the SAS website or elsewhere on the Internet, and then include it (or %include it) in your program before you call it.
@Ronein wrote:
How can I do it please?
Should I add one more %let statement?
No. You simply program the logic yourself, using standard objects provided by SAS, in this case dictionary.tables:
data use_this_if_no_obs;
msg = 'No Data';
run;
data tbl_null;
if 0;
run;
proc sql noprint;
select
case
when nobs = 0
then 'use_this_if_no_obs'
else memname
end as ds into :ds
from dictionary.tables
where libname = 'WORK' and memname = 'TBL_NULL';
quit;
proc print data=&ds;
run;
Or you get the code for the macro and include it either in the global autocall library of your SAS installation, or in all programs where you use it.
data use_this_if_no_obs; msg='No Data'; run; data _null_; set sashelp.vtable (where=(libname="WORK" and memname="ABC")); if nobs=0 then call execute('proc print data=use_this_if_no_obs; run;'); else call execute('proc print data=abc; run;'); run;
This will print use_this_if_no_obs is there are no observations in WORK.ABC. Otherwise it will print WORK.ABC.
Perfect!
How can I see it in Results window and not in log window?
@Ronein wrote:
Perfect!
How can I see it in Results window and not in log window?
Look at your output window. @RW9's code has proc print in the call execute, so it will create output (unless you have no ODS destination open).
His code applied to your example:
data use_this_if_no_obs;
msg = 'No Data';
run;
data tbl_null;
if 0;
run;
data _null_;
set sashelp.vtable (where=(libname="WORK" and memname="TBL_NULL"));
if nobs=0 then call execute('proc print data=use_this_if_no_obs; run;');
else call execute('proc print data=abc; run;');
run;
gives this result:
Obs msg 1 No Data
when list output is active in EG.
I run the following code and unable to open the excel file.
I get an error in excel "unable to read the file"
why???
ods path work.temptemp(update) sasuser.templat(update)
sashelp.tmplmst(read);
ods path show;
ods tagsets.excelxp file="/my personal computer address/w.xls"
style=htmlblue
OPTIONS( embedded_titles='yes'
SHEET_NAME="Joe"
absolute_column_width="17,20,7,9,9,9,11,13,11,11,11"
sheet_interval="NONE" );
data use_this_if_no_obs;
msg = 'No Data';
run;
data tbl_null;
if 0;
run;
data _null_;
set tbl_null;
if nobs=0 then call execute('proc print data=use_this_if_no_obs; run;');
else call execute('proc print data=abc; run;');
run;
ods tagsets.excelxp close;
ods _all_ close;
You are trying to lie to Excel. The output from tagsets.excelpxp is not a xls, it's XML, so name your file accordingly:
ods tagsets.excelxp file="/my personal computer address/w.xml"
Newer versions of Excel complain if the filename extension doesn't fit with the contents.
The resulting file is readable with a simple text editor (notepad++ or similar) for control. You may have to open it manually from Excel, unless you associate .xml files with Excel in the Windows Explorer.
@RW9 If you going to use that type of a program then you might just skip accessing the metadata and instead just reference the dataset itself.
data _null_;
if eof then call execute('proc print data=use_this_if_no_obs; run;');
else call execute('proc print data=abc; run;');
stop;
set WORK.ABC end=eof;
run;
Although I think both methods will fail if WORK.ABC does not exist.
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.