DATA Step, Macro, Functions and more

PROC SQL NOPRINT and RESOLVE function

Reply
New Contributor
Posts: 4

PROC SQL NOPRINT and RESOLVE function

 

 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 

PROC Star
Posts: 1,760

Re: PROC SQL NOPRINT and RESOLVE function

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.

New Contributor
Posts: 4

Re: PROC SQL NOPRINT and RESOLVE function

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;

PROC Star
Posts: 1,760

Re: PROC SQL NOPRINT and RESOLVE function

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; 

 

Ask a Question
Discussion stats
  • 3 replies
  • 144 views
  • 0 likes
  • 2 in conversation