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.

CodePudding user response：

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.

CodePudding user response：

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

CodePudding user response：

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.

CodePudding user response：

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;