DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Contributor
Posts: 29
Accepted Solution

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

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


Accepted Solutions
Solution
‎03-14-2014 01:37 PM
PROC Star
Posts: 1,231

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

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


View solution in original post


All Replies
PROC Star
Posts: 1,231

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

Before the proc sql step, add:

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

Contributor
Posts: 29

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

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;

Solution
‎03-14-2014 01:37 PM
PROC Star
Posts: 1,231

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

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


Regular Contributor
Posts: 194

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

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.

Valued Guide
Posts: 2,175

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

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.

Contributor
Posts: 29

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

Thank you Quentin!

Tested and sold!

Also, I found using %symexist() also solve my problemSmiley Happy

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

/* %goto exit2;*/

/*%end;*/

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 1037 views
  • 0 likes
  • 4 in conversation