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
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
Make sure that it always exist by using %global before your SQL.
Still, you might to have some logic when Name_i is blank.
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.
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
--*;
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;
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
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.