topic Re: proc sql noprint - affects all proc sql's in program in SAS Programming

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

jamesBFD — Mon, 13 May 2019 15:59:11 GMT

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?

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

ballardw — Mon, 13 May 2019 16:03:54 GMT

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.

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

Reeza — Mon, 13 May 2019 16:27:45 GMT

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?

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

Tom — Mon, 13 May 2019 17:57:38 GMT

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;

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

jamesBFD — Tue, 14 May 2019 08:52:15 GMT

Thanks, the reset print statement worked perfectly.