BookmarkSubscribeRSS Feed
MustafaAbdelhaq
Calcite | Level 5

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

1 REPLY 1
jwillis
Quartz | Level 8

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.

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 1 reply
  • 2692 views
  • 0 likes
  • 2 in conversation