topic Re: assign a value to Macro variable when no rows were selected from table in SAS Programming

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

jay_q — Fri, 14 Mar 2014 16:01:04 GMT

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

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

Quentin — Fri, 14 Mar 2014 16:16:44 GMT

Before the proc sql step, add:

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

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

jay_q — Fri, 14 Mar 2014 16:35:31 GMT

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;

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

Quentin — Fri, 14 Mar 2014 17:37:06 GMT

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. : )

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

gamotte — Fri, 14 Mar 2014 17:41:46 GMT

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.

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

Peter_C — Fri, 14 Mar 2014 17:44:32 GMT

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.

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

jay_q — Fri, 14 Mar 2014 17:56:37 GMT

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;*/