topic Running a second query if the first query returns nothing? in SAS Web Report Studio

Running a second query if the first query returns nothing?

MustafaAbdelhaq — Tue, 09 Jun 2015 20:34:56 GMT

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

Re: Running a second query if the first query returns nothing?

jwillis — Wed, 10 Jun 2015 13:46:20 GMT

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.