topic Re: print if data set is null in SAS Programming

print if data set is null

Ronein — Mon, 03 Sep 2018 11:59:33 GMT

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;

Re: print if data set is null

PaigeMiller — Mon, 03 Sep 2018 12:05:49 GMT

What error do you get? Show us the SASLOG.

Re: print if data set is null

Ronein — Mon, 03 Sep 2018 12:11:44 GMT

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.

Re: print if data set is null

Kurt_Bremser — Mon, 03 Sep 2018 12:12:04 GMT

%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.

Re: print if data set is null

Ronein — Mon, 03 Sep 2018 12:14:37 GMT

How can I do it please?

Should I add one more %let statement?

Re: print if data set is null

PaigeMiller — Mon, 03 Sep 2018 12:19:52 GMT

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.

Re: print if data set is null

Kurt_Bremser — Mon, 03 Sep 2018 12:27:12 GMT

@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.

Re: print if data set is null

RW9 — Mon, 03 Sep 2018 12:29:03 GMT

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.

Re: print if data set is null

Ronein — Mon, 03 Sep 2018 12:42:55 GMT

Perfect!

How can I see it in Results window and not in log window?

Re: print if data set is null

Kurt_Bremser — Mon, 03 Sep 2018 12:47:56 GMT

@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.

Re: print if data set is null

Ronein — Mon, 03 Sep 2018 12:57:30 GMT

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;

Re: print if data set is null

Kurt_Bremser — Mon, 03 Sep 2018 13:16:08 GMT

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.

Re: print if data set is null

Tom — Mon, 03 Sep 2018 15:48:44 GMT

@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.