Desktop productivity for business analysts and programmers

Within macro, how to stop later procedure one of the internal data file has no observation

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 92
Accepted Solution

Within macro, how to stop later procedure one of the internal data file has no observation

[ Edited ]

Hi,

 

I am using SAS EG to build a macro function. 

 

It reads initial input data line by line (each is an individual spot) and final output is by each spot level. 

Some times for one spot, one of the internal data does not have any observation (This spot does not exist in the system in Oracle Database) 

 

My code looks like this form:

 

%macro mymacro;
OPTIONS NOTES SOURCE SOURCE2 MPRINT MLOGIC MERROR SYMBOLGEN;

 

/* Define the initial input file as well as its structure */

data _null_;
    set Input end=last;
    if last then call symput('nfiles',_n_);
run;

 

/* Read input data line by line */

%do i=1 %to &nfiles;

data _null_;
     set Input;

     if &i=_n_ then
     call symput('Spot',Spot);
run;

 

/* The following reads data from Oracle Database */ 

Proc Sql; 

      connect to oracle (user=..... orapw=..... path=.....);
      create table Supply_&Spot as
      select * from connection to oracle

(

....

);

      disconnect from Oracle;

Quit;

 

 

/* Sometimes, some specific spots does not exist in the system and I woule like to stop the following procedure if that happens */

 

Data Supply2_&Spot;

     Set Supply_&Spot;

     ...........;

     ...........;

Run;

 

 

......

......

 

%end;

%mend;

 

%mymacro;

 

 

I am trying to add this procedure after Data I/O from Oracle database:

 

%if %sysfunc(exist(Supply_&Spot)) %then
%do;

   ....... (Following procedures ...) 

%end;

 

%else %stop; (Is this line correct?)

 

 

But it seems I cannot use this procedure as if a specific spot doesnot exist in the Oracle, it will also return data Supply_&Spot, but has no observations. So that I cannot use 'exist'. 

 

Is there any way to fix it? So that if that dataset has 0 observation, I can stop the process; otherwise, it will continue to run the following procedures.

 

Thank you!  


Accepted Solutions
Solution
‎06-15-2017 03:10 PM
Respected Advisor
Posts: 4,973

Re: Within macro, how to stop later procedure one of the internal data file has no observation

There's an automatic variable &SQLOBS that SQL produces, showing the number of observations processed.  You should be able to condition on that being greater than 0 to control subsequent procedures.

View solution in original post


All Replies
Solution
‎06-15-2017 03:10 PM
Respected Advisor
Posts: 4,973

Re: Within macro, how to stop later procedure one of the internal data file has no observation

There's an automatic variable &SQLOBS that SQL produces, showing the number of observations processed.  You should be able to condition on that being greater than 0 to control subsequent procedures.

Frequent Contributor
Posts: 92

Re: Within macro, how to stop later procedure one of the internal data file has no observation

That works. Thanks!

 

 

☑ This topic is SOLVED.

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

Discussion stats
  • 2 replies
  • 86 views
  • 0 likes
  • 2 in conversation