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

Crubal — Thu, 15 Jun 2017 15:27:20 GMT

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!

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

Astounding — Thu, 15 Jun 2017 15:33:59 GMT

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.

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

Crubal — Thu, 15 Jun 2017 19:11:04 GMT

That works. Thanks!

topic Re: Within macro, how to stop later procedure one of the internal data file has no observation in SAS Enterprise Guide

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

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

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