BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
bnarang
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
AhmedAl_Attar
Rhodochrosite | Level 12
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

View solution in original post

5 REPLIES 5
LinusH
Tourmaline | Level 20

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

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

Data never sleeps
Astounding
PROC Star

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
Obsidian | Level 7

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
Super User Tom
Super User

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
Rhodochrosite | Level 12
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 4753 views
  • 0 likes
  • 6 in conversation