How to run a different query if table is empty one month earlier

Question

How to run a different query if the output table is empty.

My current query is:

PROC SQL;
   CREATE TABLE WORK.QUERY_FOR_A_KUNDESCORINGRATINGRE AS 
   SELECT t1.PD, 
          t1.DATO, 
          t1.KSRUID
      FROM DLKAR.A_KUNDESCORINGRATINGRETRO t1
      WHERE t1.KSRUID = 6 AND t1.DATO = '31Aug2022'd;
QUIT;

But I would like to make a conditional statement to run the query again if it is empty but with the filter t1.DATO set to '31Jul2022'd instead of august. So every time the query fails on a given date the query tries again one month earlier.

I hope you can point me in a direction.

Should checking for previous month happens only once, or until the table is not empty? — Negdo, Sep 30 '22 at 09:48
What is one month earlier than 30jun2022: 30may2022 or 31may2022? — Dirk Horsten, Sep 30 '22 at 11:12
Could your dataset have observations(=rows) for 1aug2022, but not for 31aug2022? — Dirk Horsten, Sep 30 '22 at 11:13
@DirkHorsten good question in this case it is 31may2022. In the data set there is no data for 1aug2022 — Christoffer, Sep 30 '22 at 12:11

Dirk Horsten · Answer 1 · 2022-09-30T11:14:38.357

I assume you always want to query for DATO the last day of the month.

%macro QUERY_FOR_A_KUNDESCORINGRATINGRE(DATO);
** Try with the date given **;
PROC SQL;
   CREATE TABLE WORK.QUERY_FOR_A_KUNDESCORINGRATINGRE AS 
   SELECT t1.PD, 
          t1.DATO, 
          t1.KSRUID
      FROM DLKAR.A_KUNDESCORINGRATINGRETRO t1
      WHERE t1.KSRUID = 6 AND t1.DATO = &DATO;
QUIT;

** Read the result AND any other dataset. 
   (SASHELP.CLASS is a small dtaset that always exists.) **;
data _null_;
    set QUERY_FOR_A_KUNDESCORINGRATINGRE(in=real_result) SASHELP.CLASS;

    ** If there is any result, the first observation(=row) will be from
       that result  and real_result will be 1(the SAS coding of True)
       otherwise real_result will be 0(the SAS coding of False) **;
    if not real_result then do;
       ** find the last day of the previous month **;
        month_earlier = intnx("month", -1, &DATO, 'end');
        call execute('%QUERY_FOR_A_KUNDESCORINGRATINGRE('
            || put(month_earlier, 8.) ||');');
    end;
    ** We only need one observation(=row), so stop now **;
    stop;
run;

%mend;
%QUERY_FOR_A_KUNDESCORINGRATINGRE('31Aug2022'd);

Disclaimer: I did not test this. It might need some debugging.

score 1 · Answer 2 · edited Sep 30 '22 at 14:18

Try to run this code, we need loop until you get records

%macro query;
    %global DATO;
    %let DAT0 = '31Aug2022'd;
    %first: PROC SQL;
       CREATE TABLE WORK.QUERY_FOR_A_KUNDESCORINGRATINGRE AS 
       SELECT t1.PD, 
              t1.DATO, 
              t1.KSRUID
          FROM DLKAR.A_KUNDESCORINGRATINGRETRO t1
          WHERE t1.KSRUID = 6 AND t1.DATO = &DATO;
    QUIT;
    
    %let dsid = %sysfunc(open (QUERY_FOR_A_KUNDESCORINGRATINGRE))
    %let obs = %sysfunc(attrn(&dsid. nlobs));
    %let dsid = %sysfunc(close(&dsid.));
    
    %if &obs = 0 %then %do; 
        data _null_;
            call symputx("dato",intnx('m',&dato.,-1));
        run;
        %goto first;
    %end;
%mend;
%query;

Please note: I haven't tested this code would be great if this helps you

Tom · Accepted Answer · 2022-09-30T13:53:50.990

Just loop until you get results. You should put an upper bound on the number of times it loops to make sure if will end.

This will require that you create a macro to allow the conditional code generation.

%macro loop(start,months);
%local offset;
PROC SQL;
%do offset=0 to -&months by -1;

CREATE TABLE WORK.QUERY_FOR_A_KUNDESCORINGRATINGRE AS 
   SELECT t1.PD
        , t1.DATO
        , t1.KSRUID
      FROM DLKAR.A_KUNDESCORINGRATINGRETRO t1
      WHERE t1.KSRUID = 6
        AND t1.DATO = %sysfunc(intnx(month,&start,&offset,e))
;
  %if &sqlobs %then %goto leave;
%end;
%leave:
QUIT;
%mend;

%loop('31AUG2022'd,6)

You could make SQL work a little harder to get what you want. Pull the data back as many months as you want, but only keep the observations that are for the latest month. Now you don't need any looping.

CREATE TABLE WORK.QUERY_FOR_A_KUNDESCORINGRATINGRE AS 
   SELECT t1.PD
        , t1.DATO
        , t1.KSRUID
      FROM DLKAR.A_KUNDESCORINGRATINGRETRO t1
      WHERE t1.KSRUID = 6
        AND t1.DATO between %sysfunc(intnx(month,&start,-&offset,e)) and &start
   having dato=max(dato)
;

Which method performs better will depend on the data and things like whether or not the data is sorted or indexed.

score 1 · Answer 4 · answered Sep 30 '22 at 14:24

If your dataset only contains data for the last day of a month, this solves your problem without iterating at all:

PROC SQL;
   CREATE TABLE WORK.QUERY_FOR_A_KUNDESCORINGRATINGRE AS 
   SELECT t1.PD, 
          t1.DATO, 
          t1.KSRUID
      FROM DLKAR.A_KUNDESCORINGRATINGRETRO t1
      WHERE t1.KSRUID = 6 AND t1.DATO = (
         SELECT max(t2.DATO)
            FROM DLKAR.A_KUNDESCORINGRATINGRETRO t2
            WHERE t2.KSRUID = 6);
QUIT;

How to run a different query if table is empty one month earlier

4 Answers4