topic proc print data where in SAS Procedures

proc print data where

Citrine10 — Fri, 01 Apr 2022 08:25:02 GMT

hi I would like for my dataset to not be printed to that it doesnt send an email when there are no records. The below is not working though:

proc print data=mydata; 
(where noobs > 0);
run;

ERROR 180-322: Statement is not valid or it is used out of proper order.

Re: proc print data where

Kurt_Bremser — Fri, 01 Apr 2022 08:35:33 GMT

You can either have a WHERE= dataset option:

proc print data=mydata (where=(noobs > 0));
run;

or a WHERE statement:

proc print data=mydata;
where noobs > 0;
run;

but if you want to only run the PROC PRINT when your dataset has observations, you must do this:

%let noobs = 0; /* in case dataset does not exist */
proc sql noprint;
select nobs int :noobs from dictionary.tables
where libname = "WORK" and memname = "MYDATA";
quit;

%if &noobs. > 0
%then %do;

proc print data=mydata;
run;

%end;

Re: proc print data where

Citrine10 — Fri, 01 Apr 2022 08:42:30 GMT

thank you Kurt, but what do I do in the instance where there is no observations as there is no records as in that case there wouldnt be an obs column
ERROR: Variable obs is not on file WORK.Data

Re: proc print data where

Kurt_Bremser — Fri, 01 Apr 2022 08:44:10 GMT

Please post your complete log of trying the code I gave you.

Re: proc print data where

Citrine10 — Fri, 01 Apr 2022 08:53:50 GMT

proc print data=data(where=(obs > 0));
ERROR: Variable obs is not on file WORK.data

Re: proc print data where

Citrine10 — Fri, 01 Apr 2022 08:58:55 GMT

I also tried the below and get an error:

%let noobs = 0; /* in case dataset does not exist */
proc sql noprint;
select nobs into :noobs from mydata
quit;
%if &noobs. > 0
%then %do;

FILENAME MailBox EMAIL
TO=(jackie@gmail.com)

FROM='henr@gmail.com'
type='text/html'
SUBJECT='Table';

ODS html body=Mailbox style = noline;
ods listing close;

proc print data=mydata
style (report) = {background = white
font_face = "Verdana" font_size = 7pt just=left bordercolor=grey rules=All frame=box}
style (column) = {background = white CELLHEIGHT = 2.5%
font_face = "Verdana" font_size = 7pt just=left }
style (header) = {foreground = cx5e2750 font_face="Verdana"
font_size = 8pt just=left
};
title1 "Table";
run;
ods html close;
%end;

ERROR: The following columns were not found in the contributing tables: nobs.

Re: proc print data where

yabwon — Fri, 01 Apr 2022 09:20:36 GMT

An alternative to dictionary tables:

%macro printMe(DS);
data _null_;
  if nobs then call execute (" proc print data=&DS.; run; ");
  stop;
  set &DS. nobs=nobs;
run;
%mend printMe;

%printMe(sashelp.class);


data test;
  x = 42;
  stop;
run;


%printMe(test);

All the best

Bart

Re: proc print data where

Kurt_Bremser — Fri, 01 Apr 2022 09:24:07 GMT

You cannot use a variable in the WHERE that does not exist in the dataset.

Please explain in clear terms what you want to do.

Re: proc print data where

Kurt_Bremser — Fri, 01 Apr 2022 09:29:02 GMT

You do not use my code. My code reads the metadata of the MYDATA table from DICTIONARY.TABLES. Please study my code in detail, and, as already asked, please explain in detail what you want to do.

Re: proc print data where

ghosh — Sun, 03 Apr 2022 17:58:49 GMT

%macro prnt;
   %if %sysfunc(exist(work.mydata)) %then %do;
      proc print data = work.mydata;
      run;
   %end;
   %else %put dataset does not exist;
 %mend prnt;

%prnt;

Re: proc print data where

Sajid01 — Thu, 14 Apr 2022 21:24:23 GMT

There are many approaches to solve your issue. I have used the following and it should serve your purpose i.e., print the file if there are observations in the dataset and not when empty.

I have created an empty dataset called class from sashelp.class and used it to illustrate my code.

/*Creating an empty dataset*/
proc sql;
create table class like sashelp.class;
quit;
%macro print_if_not_empty(dataset_name);
/* finding the number of observations.*/
data _null_;
set &dataset_name. nobs=obs;
call symputx('nobs',nods);
run;
%if %sysfunc(symexist(nobs)) %then %do;
proc print data=&dataset_name.;
run;
%end;
%else %do;
%put ****The dataset &dataset_name. is empty *****;
%end;
%mend;

/*Call the macro */

%print_if_not_empty(class)