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 &¯o_out&j;
quit;
Lei
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. : )
Before the proc sql step, add:
%let macro_out&j = /*whatever value you want iuf now rows are selected by SQL step*/ ;
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 &¯o_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 &¯o_out&j;
quit;
%let con_exit2=0;
%if &¯o_out&j = &con_exit2 %then %do;
%goto exit2;
%end;
%exit2:
%end;
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. : )
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.
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.
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;*/
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.