Conditionally print empty table or non empty table

[Ronein]

Hello

I am trying again to print a table.

If the table is empty then I want to print a message that there is no data.

I am running 2 cases.

Case1 is when the table is not empty and then we will have a regular print of the table

Case2 is when a table is empty and then we need to print a message that "no Data"

When I run the code then in Case2 I don't get any print. Why???

/*Case1*/
data use_this_if_no_obs;
msg = 'No Data';
run;
data tbl_rawData1;
x=1;
run;
data _null_;
  set tbl_rawData1;
  if  nobs=0 then call execute('proc print data=use_this_if_no_obs; run;');
  else call execute('proc print data=tbl_rawData1; run;');
run;

/*Case2*/
data use_this_if_no_obs;
msg = 'No Data';
run;
data tbl_rawData2;
If 0;
run;
data _null_;
  set tbl_rawData2;
  if  nobs=0 then call execute('proc print data=use_this_if_no_obs; run;');
  else call execute('proc print data=tbl_rawData2; run;');
run;

[andreas_lds]

There should be a note in the log complaining about "nobs" not being initialized.

You have to use the nobs-option in the set-statement, to get the number of observations of the dataset used.

data _null_;
  if 0 then set tbl_rawData2 nobs=nobs;
  if  nobs=0 then call execute('proc print data=use_this_if_no_obs; run;');
  else call execute('proc print data=tbl_rawData2; run;');
run;

I used the "if 0 then" construct to avoid that the dataset is actually loaded and proc print is called only once.

[Ronein]

Can you  please explain your code?

It is working perfect but I cannot understand it.

I don't understand the following code:
"if 0 then set tbl_rawData2 nobs=nobs;"

[Kurt_Bremser]

The set statement has two elements, declarative and executive.

declarative: it tells the SAS compiler to read the metadata of the dataset and create place for the variables in the PDV. It also sets things like the nobs= variable.

executive: read an observation from the dataset, until eof is reached.

The statement forces the data step compiler to honor the declarative part, but prevents the running of the executive part.

[ChrisNZ]

This code cannot work as intended. The test will never take place if the table is empty (though I am unsure where your NOBS variable comes from).

data _null_;
  set tbl_rawData1;
  if  nobs=0 then ... ;
run;

Is this what you want?

data _null_;
  if NOBS=0 then ...;
  set TABLE nobs=NOBS;
run;

[Kurt_Bremser]

You already accepted a solution to this issue in https://communities.sas.com/t5/SAS-Programming/print-if-data-set-is-null/m-p/492046.

Please study the examples given there carefully, and if you use them, take care of the statements and all their elements and the order in which they are written. All of this is important.

[sb51469]

Hello @Ronein,

As @ChrisNZ said the code will not work that way. I did understood your problem though. The problem with nobs option is that its not foolproof with empty datasets. the best way to go in this case is the attrn function. here is what you should be doing for attrn to work.

data _null_;

   dsnid=open('tbl_rawdata1');

   if dsnid then nobs=attrn(open('tbl_rawdata1'), 'nobs' );

   if nobs=0 then call execute('proc print data=use_this_if_no_obs; run;');

   else  call execute('proc print data=tbl_rawdata1; run;');

run;

[andreas_lds]

Sorry, @sb51469, but your solution does not work if all obs have been delete from "tbl_rawdata1".

data work.tbl_rawdata1;
   set sashelp.class;
run;

proc sql noprint;
   delete from work.tbl_rawdata1
      where Age < 20;
quit;

data use_this_if_no_obs;
   msg = 'No Data';
run;

data _null_;
   dsnid=open('work.tbl_rawdata1');

   if dsnid then do;
      nobs=attrn(dsnid, 'nobs');
   end;

   if nobs=0 then do;
      call execute('proc print data=use_this_if_no_obs; run;');
   end;
   else do;
      call execute('proc print data=work.tbl_rawdata1; run;');
   end;

   rc = close(dsnid);
run;

Replacing "nobs" with "nlobs" in function attrn solves that issue.

Can you explain  "The problem with nobs option is that its not foolproof with empty datasets. the best way to go in this case is the attrn function." further?

[sb51469]

@andreas_lds, thanks for correcting my mistake.

On the 'nobs being not a foolproof' comment, actually it occurred with me once while I was creating this report where a dataset didn't had any observations, I was using the nobs= option to find out if it was empty or not and I was always getting 1 as the answer instead of 0. Didn't understood what exactly was the problem there.

Then I switched on to the nlobs option in attrn and yeah I mentioned it wrongly here as nobs, my bad. Since I work for an institution where I have signed a Non-disclosure, I don't directly copy codes from my EG to here. I just type everything, so typo errors do occur in my reply.

[RW9]

Still not sure what is wrong with the code I presented to you?  If the table may not exist, then you should have stated that in your question - I can only work with what you post.  A simple fix is:

data  use_this_if_no_obs;
  msg='No Data';
run;

data _null_;
  set sashelp.vtable (where=((libname="WORK" and memname="ABC") or (libname="SASHELP" and memname="CARS")));
  by libname;
  if (first.libname and last.libname) or (memname="ABC" and nobs=0)
    then call execute('proc print data=use_this_if_no_obs; run;');
  else call execute('proc print data=abc; run;');
run;

I.e. use a table you know is always there so that something is always returned. 

[sb51469]

@RW9, your solution's also fine, but I think this guy is having some problems with SASHELP.VTABLE. But then yeah he should specify more about his problem.

[s_lassen]

I would put this in a macro, as it sounds like you want to do it more than once:

%macro print(data);
  %local PrintData;
  %let PrintData=_no_obs_in_data;
  data _no_obs_in_data;
    msg="&data is empty";
    output;
    set &data;
    call symput('PrintData',"&data"); /* only gets executed if there are data */
    keep msg;
  run;
  proc print data=&PrintData;
  run;
%mend;
%print(tbl_rawData1);
    

[Tom]

The reason is that a SAS data step stops when it reads past the end of the input. By have the SET statement first in the data step it never gets to the other statements when the dataset is empty.

The trouble with using NOBS (or even NLOBS) variables available via metadata query (or even the NOBS= option on SET statement) is that it does not work for views.

You can instead use the END= option on the SET statement.

data _null_;
  if eof then call execute('proc print data=use_this_if_no_obs; run;');
  else call execute('proc print data=tbl_rawData2; run;');
  stop;
  set tbl_rawData2 end=eof;
run;

Note that this will fail if the input dataset does not exist at all.  If you need to handle that case without SAS generating errors then you will need add more logic.

[sb51469]

@Tom, that's a good perspective to solve this problem from every angle. Thanks for the solution.