topic Re: Conditional Check if Macro variable value not resolved? in SAS Programming

Conditional Check if Macro variable value not resolved?

bnarang — Mon, 26 Nov 2012 06:59:51 GMT

Hi All

First of all, thank you so much for resolving my queries all these days. My question is: Say, I am creating a macro variable with possible values using

Proc sql;

Select name into : name_i from

have where ..;

quit;

Now, say for example, if no rows were selected from the "Have" table because of certain conditions. If on an production point of view. I wrote my next program like this:

Data Want;

Set have;

If name in (&name_i.) then condition.;

run;

In case, if name_i. not resolved, it would give me errors. How to avoid?

Thanks

Re: Conditional Check if Macro variable value not resolved?

LinusH — Mon, 26 Nov 2012 09:03:03 GMT

Make sure that it always exist by using %global before your SQL.

Still, you might to have some logic when Name_i is blank.

Re: Conditional Check if Macro variable value not resolved?

Astounding — Mon, 26 Nov 2012 14:31:20 GMT

Each SELECT statement updates the automatic macro variable &SQLOBS. If all of your code appears inside a macro definition, you could try:

data want;

set have;

%if &sqlobs > 0 %then %do;

* add whatever SAS statements you would like;

%end;

run;

There are complications. Any subsequent SQL SELECT statement replaces &SQLOBS. And there can be issues of %local vs. %global. But &SQLOBS should be the right tool for the job.

Good luck.

Re: Conditional Check if Macro variable value not resolved?

chang_y_chung_hotmail_com — Mon, 26 Nov 2012 15:27:15 GMT

I would suggest not creating name_i macro variable at all. Here is a simple example. Hope this helps a bit.

/* create test datasets */
proc sql;
    create table have as select name, height, weight from sashelp.class;
    create table names as select name, age from sashelp.class;
quit;

/* given an age, create a dataset want that subsets indirectly through name */
%macro ageOf(age, data=have, out=want, names=names, debug=0);
    %if %superq(age)= %then %return;

    %local print;
    %if &debug %then %let print = %nrstr(select &age as age, * from &out;);

    proc sql;
      create table &out as
      select *
      from &data as d
      where d.name in (select name from &names where age = &age);
      %unquote(&print)
    quit;
%mend ageOf;

%ageOf(10, debug=1)
%*-- on log
NOTE: Table WORK.WANT created, with 0 rows and 3 columns.
--*;

%ageOf(11, debug=1)
%*-- on lst
       age Name        Height    Weight
--------------------------------------
        11 Joyce         51.3      50.5
        11 Thomas        57.5        85
--*;

Re: Conditional Check if Macro variable value not resolved?

Tom — Mon, 26 Nov 2012 17:27:44 GMT

The easiest way to deal with the issue of SQL finding no matches is to set a value for the macro variable before the SELECT statement.

proc sql noprint;

%let name_i=;

select name

into :name_i

from have

where ...

;

quit;

Re: Conditional Check if Macro variable value not resolved?

AhmedAl_Attar — Mon, 26 Nov 2012 17:45:17 GMT

Bhpinder,

Try using the %symexist Function. It returns an indication of the existence of a macro variable. Check the Help documentation for usage examples.

Hope this helps,

Ahmed