assign a value to Macro variable when no rows were selected from table

[jay_q]

Hello,

Anyone has an idea how we can assign a value to Macro variable when no rows were selected below? Thanks for any inputs.

proc sql;

select count into : macro_out&j from macro_out;

%put &&macro_out&j;

quit;

Lei

[Quentin]

Before the proc sql step, add:

%let macro_out&j = /*whatever value you want iuf now rows are selected by SQL step*/  ;

[jay_q]

Hi Quentin,

Sorry I wasn't clear about the process. I have a do loop. After each loop, Count(*) from ABC table decreases until no values displayed.  My problem is when no values displayed, Macro variable &&macro_out&j would not get assigned and resolved.  So %if condition would generate error. Thanks.

%do i=1 %to 100;

proc sql;

..............

proc sql;
create table macro_out as
(select exclude_ind,link_id,count(*)as count
from   ABC

where  ind = 'N'
group  by 1,2
having link_id is null);

select count into : macro_out&j from macro_out;
%put &&macro_out&j;
quit;

%let con_exit2=0;

  %if &&macro_out&j = &con_exit2 %then %do;

    %goto exit2;

  %end;

%exit2:

%end;

[Quentin · Accepted solution]

Hi,

Sticking with my original suggestion. : )

Add  %let macro_out&j=0;   before your SQL step

When the SQL step runs:

  if there are records in macro_out then count will be >0 and macro_out&j will be assigned the value of count.

  if there are no records in macro_out (because no records that satisfy the having clause), then select count into : macro_out&j will not execute.  And the value of macro_out&j will remain as 0.

As I understand it. : )

[gamotte]

As I understand your program, even if the number of rows of ABC has decreased to zero, your table macro_out

should have one row with count=0 and missing values for the other variabes, so your mv macro_out&j. should be valued.

In your example, You are looping on i instead of j. Can this be the explanation ?

You can test the macrovariable &sqlobs. to check if your SQL request returned something.

[Peter_C]

When where returns no rows for a proc sql select statement  the procedure still sets the number of selected rows (0) in the SQLOBS macro var.

so use &sqlobs as that number of rows.

[jay_q]

Thank you Quentin!

Tested and sold!

Also, I found using %symexist() also solve my problem:)

/*%if %symexist(macro_out&j)=0 %then %do;*/

/* %goto exit2;*/

/*%end;*/