topic Re: Can you control a PROC SQL statement not to print? in New SAS User

Can you control a PROC SQL statement not to print?

cosmid — Thu, 29 Apr 2021 21:51:26 GMT

data example;
input x $ y;
datalines;
a 1
c 5
run;

proc sql;
  select * from example
  where x = 'c';
quit;

proc sql;
  select * from example
  where x = 'd';
run;

The first PROC SQL will print the result, but the second PROC SQL will print out an empty dataset. If I have dozens of these PROC SQL statements, is there a way to have the PROC SQL print out only if there is a matching record and PROC SQL like the second one not to print anything to the output window? I tried using a Having and Group By. It worked but prints out an extra CNT column.

proc sql;
select x, count(*) as CNT
from example
group by x
having CNT > 0;
quit;

I have thought about creating a new dataset just to store the results and do another PROC SQL to get the result off that dataset. I don't think it's very efficient.

Thanks in advance for your help!

Re: Can you control a PROC SQL statement not to print?

ballardw — Thu, 29 Apr 2021 22:14:41 GMT

Proc SQL is inefficient for simple printing and Proc print doesn't create output when there is nothing to print because of a WHERE statement.

proc print data=example ;
where x='d';
run;

Re: Can you control a PROC SQL statement not to print?

Aku — Mon, 03 May 2021 12:32:34 GMT

In Proc SQL there is noprint option, so use it.

Proc sql noprint;

select * from example

where x= 'd';

quit;

Re: Can you control a PROC SQL statement not to print?

PGStats — Mon, 03 May 2021 12:57:28 GMT

 70         proc sql;
 71           select * from example
 72           where x = 'd';
 NOTE: No rows were selected.
 73         quit;

A note is added to the LOG,

and nothing is printed in the results.

Re: Can you control a PROC SQL statement not to print?

cosmid — Tue, 04 May 2021 18:01:49 GMT

I know about the noprint. It is not ideal for my situation because the content of the datasets change so I only want it to print if it is not an empty data set.

Re: Can you control a PROC SQL statement not to print?

cosmid — Tue, 04 May 2021 18:04:10 GMT

I haven't tested this but I think that's when you run one code on one dataset. I'm running a bunch of code on a bunch of dataset. So in the output window, I'll have the non-empty dataset along with empty data sets with just their dataset names. So I was looking for a solution to get rid off these empty datasets. Something like:
if nobs > 0;
proc print data=data_name;
run;
I guess there's no such thing.

Re: Can you control a PROC SQL statement not to print?

cosmid — Tue, 04 May 2021 18:06:37 GMT

Hi ballardw,

Thanks for replying to all of my posts. I find a solution to the error message problem. If I use Quick Reply instead of Reply, then it will let me reply to posts. but unfortunately, there's not a Quick Post button.

Is there a way to reference the number of observations in a dataset? like:

proc print data=example;
where number_of_obs > 0;
run;

or something like

if example.number_of_obs > 0 then
proc print data=example;
run;

Re: Can you control a PROC SQL statement not to print?

cosmid — Tue, 04 May 2021 18:57:39 GMT

I resolved the problem, not very efficient but it got what I wanted. I wrote saved the number of obs of the dataset into a mac var and then used that mac inside a macro with a if statement.