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!
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.
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.
That works. Thanks!
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!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.