topic Re: PROC SQL NOPRINT and RESOLVE function in SAS Programming

PROC SQL NOPRINT and RESOLVE function

dostiep — Fri, 10 Nov 2017 07:40:19 GMT

Hi,

I was wondering why, when I add the option NOPRINT to PROC SQL, only the first line generated by the macro call from RESOLVE function is displayed in the LOG? I f I remove the NOPRINT option, all the expected lines are written in the LOG.

If you run the following code, you'll see only one line displayed in the LOG.

%macro warn(msg);

%put &msg;

%mend warn;

PROC SQL noprint;

SELECT RESOLVE('%warn( msg = name='||name||' age='||PUT(age,2.)||')') AS y

FROM sashelp.class

;

QUIT;

If you run it again but this time without the NOPRINT, you'll see all the lines displayed in the LOG.

Any reason why? Any workarounds?

Thanks

Pierre

Re: PROC SQL NOPRINT and RESOLVE function

ChrisNZ — Mon, 13 Nov 2017 03:06:16 GMT

Because proc sql returns nothing to any destination, it just stops after the first record.

The same happens if you run

proc sql noprint; 
  select ' ' as Y from SASHELP.CLASS;
quit;       
%put &=sqlobs;

Note that if you use the data in any way, all the table is read, but there must be an output.

These 3 steps will read the whole table:

proc sql noprint;   
  create table T as
  select resolve('%warn( msg = n1='||NAME||' age='||put(AGE,2.)||')')  as Y from SASHELP.CLASS;
quit;       %put &=sqlobs;

proc sql noprint; 
  select resolve('%warn( msg = n2='||NAME||' age='||put(AGE,2.)||')')  as Y into :tt separated by ',' from SASHELP.CLASS; 
quit;        %put &=sqlobs &=tt;   

proc sql ; 
  select resolve('%warn( msg = n3='||NAME||' age='||put(AGE,2.)||')')  as Y from SASHELP.CLASS;
quit;        %put &=sqlobs;

because proc sql outputs to a table, to macro variables and to the ODS destination respectively.

Re: PROC SQL NOPRINT and RESOLVE function

dostiep — Mon, 13 Nov 2017 08:52:40 GMT

The best solution I found so far is to create a _NULL_ table. It then reads all observations and avoid any unnecessary output to be generated:

PROC SQL noprint;

CREATE TABLE _NULL_ AS

SELECT RESOLVE('%warn( msg = name='||name||' age='||PUT(age,2.)||')') AS y

FROM sashelp.class

;

QUIT;

Re: PROC SQL NOPRINT and RESOLVE function

ChrisNZ — Mon, 13 Nov 2017 09:11:27 GMT

It would probably be even faster (I cant test atm) to create a single macro variable.

proc sql noprint; 
  select resolve('%warn( msg = n2='||NAME||' age='||put(AGE,2.)||')') as Y into :dummy from SASHELP.CLASS; 
quit;