06-09-2015 04:34 PM
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.
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.
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.
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.
06-10-2015 09:46 AM
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 */
%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 */
Message was edited by: James Willis. I added the code for a macro style solution.