proc sql noprint - affects all proc sql's in program

[jamesBFD]

I've got a short program that has two proc sql statements in it.

The first one will return a small 1 column dataset. I've then got a macro that looks at the number of observations returned by the proc sql. It will do different things depending on if there is more than 1 observation returned. Returning just one observation will cause a Data statement to run and show an output I want. If more than 1 observation is returned then I'm running another proc sql statement.

I don't want to display the results of the first proc sql but I do want to display the results of the second. 

However if I add a noprint option to the first proc sql then my program doesn't show either result. That isn't the behaviour I was expecting from noprint so I'm a bit confused.

I can't share any of my code on here unfortunately, so I've tried to explain it as fully as I could.

Can anyone help please?

[ballardw]

Show your code. Change anything that might be sensitive such as file path and name, variables, libraries or data set names to "generic" such as "c:\path\file.txt", var1, var2, LIB1, data1.

Once you have a "macro" involved locations of specific coding elements become very important since that generates code and you may have something buried in the macro that is setting options you forget.

[Reeza]

Are you ending your queries with a QUIT or RUN? If not, try ending each query with a quit first.

---

@jamesBFD wrote:

I've got a short program that has two proc sql statements in it.

The first one will return a small 1 column dataset. I've then got a macro that looks at the number of observations returned by the proc sql. It will do different things depending on if there is more than 1 observation returned. Returning just one observation will cause a Data statement to run and show an output I want. If more than 1 observation is returned then I'm running another proc sql statement.

I don't want to display the results of the first proc sql but I do want to display the results of the second. 

However if I add a noprint option to the first proc sql then my program doesn't show either result. That isn't the behaviour I was expecting from noprint so I'm a bit confused.

I can't share any of my code on here unfortunately, so I've tried to explain it as fully as I could.

Can anyone help please?

---

[Tom · Accepted solution]

You can turn the NOPRINT (and other options) on/off in the same PROC SQL step using the RESET statement.

For example this code will print only one list to the output.

proc sql noprint;
select name into :name_list separated by ' ' from sashelp.class where sex='M';
reset print;
select name into :name_list separated by ' ' from sashelp.class where sex='M';
reset noprint;
select name into :name_list separated by ' ' from sashelp.class where sex='M';
quit;

[jamesBFD]

Thanks, the reset print statement worked perfectly.