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

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!  

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

2 REPLIES 2
Astounding
PROC Star

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.

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!

SAS Enterprise Guide vs. SAS Studio

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.

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
  • 2 replies
  • 655 views
  • 0 likes
  • 2 in conversation