Conditional Check if Macro variable value not resolved?

[bnarang]

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

[LinusH]

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

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

[Astounding]

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.

[chang_y_chung_hotmail_com]

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

[Tom]

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;

[AhmedAl_Attar · Accepted solution]

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