Running a second query if the first query returns nothing?

[MustafaAbdelhaq]

Hello,

So I have a table in which it contains multiple columns, one of the columns contains date.

I'm using the following in the query builder to retrieve the data of the last month.

month(date_field)=month(today())-1 AND

year(date_field)=year(today())

Sometimes during the first few days of the new months, data of the previous month wouldn't be available so the query returns no results.

I want to retrieve the data of 2 months ago if this happens. This can be done using the following query builder statements. 

month(date_field)=month(today())-2 AND

year(date_field)=year(today())

What I want to accomplish here is an automated process, in which if the first query fails to retrieve data the second query should run automatically.

Any ideas?

I've thought about running a query which returns new columns with these values month(max(date_field)) - year(max(date_field)) and use them in a third query which retrieve data. So i wouldn't need the 2 queries above, unfortunately i didn't know how  to compare the results with the origin table again.

Thank you

[jwillis]

A standard base SAS approach is to query the number of observations in the just created table using the SASHELP tables.  If the number of obs is 0, then perform a macro that executes the table build with the -2 condition.  If you are using a Query to build your empty table, then the macro value &sqlobs will equal 0 and you can use the &sqlobs value to trigger the rebuild macro.

             /* create today's master from today's work file and yesterday's master */

%macro new2;

%let dsid = %sysfunc(open(work.all_complaints));

%let num  = %sysfunc(attrn(&dsid,nlobs));

%let rc   = %sysfunc(close(&dsid));

%put &=dsid &=rc &=num;

%if &num. =  and &dsid. = 0 %then %do;

%put "work.all_complaints is empty";

  data work.master_today;        /* to */

       set work.master_yesterday;    /* from */

  run;

   %end;

   %else %do;

  data work.master_today;

       set work.master_yesterday.

                              work.all_complaints;

                 run;

  %end;

%mend new2;

%new2;

Message was edited by: James Willis.  I added the code for a macro style solution.