Conditionally execute Proc sql “delete from” and “insert into” in a macro using %if %then %else

[LL5]

I wonder what would be a correct way to use %if %then %else to conditionally execute proc sql command within a macro?

Background

1. I Need to delete all rows from an existing (master) table if data is already more than several months old, or this table already contains current month’s data (in the case of a re-run)
2. Append new month’s data to the existing (master) table
3. These existing (master) tables are in a DB on MS Sql server, where the new month’s data are SAS tables generated from a SAS process.

The below codes work fine. Be noted I used macro because I have more than one table, so I will add positional parameters later.

%macro lt1;

PROC SQL;

DELETE

FROM SQL.TABLE /* MASTER TABLE*/

WHERE METRIC_DATE = &SASDATEB OR METRIC_DATE LE &SASDATE12;

INSERT INTO SQL.TABLE /* MASTER TABLE*/

SELECT * FROM SAS.TABLE; /*NEW TABLE*/

QUIT;

%MEND;

%lt1;

SYMBOLGEN:  Macro variable SASDATEB resolves to '01APR2020'D                                                                        

SYMBOLGEN:  Macro variable SASDATE12 resolves to '01NOV2019'D                                                                       

NOTE: 371655 rows were deleted from SQL.TABLE.

NOTE: 200 rows were inserted into SQL.TABLE.

However, I wonder if there is a way to use %if %then %else to execute these command?  

I have been thinking something like below, but obviously it did not work because it just deleted all rows from the master table.

Would be great if anyone could give me some advices.

%MACRO LT ;

%IF METRIC_DATE EQ &SASDATEB OR %SYSEVALF(METRIC_DATE LT &SASDATE12) %THEN %DO;

PROC SQL;

DELETE FROM SQL.TABLE; /* MASTER TABLE*/

%END;

%ELSE %DO;

INSERT INTO SQL.TABLE/* MASTER TABLE*/

SELECT * FROM SAS.TABLE; /*NEW TABLE*/

%END;

QUIT;

%MEND;

NOTE: 371855 rows were deleted from ME.SW_EOL_CSI_ACI_T1M.

[DavePrinsloo]

I would suggest you look into partitioning in the database. That way you can drop old partititions almost instantly, and the database table is not locked whilst adding thousands of records.

[LL5]

Thanks Dave for this idea. I learned partitioning is the database process where very large tables are divided into multiple smaller parts.

I wonder how would I drop old partitions instantly, would you mind to give me an example?

[ballardw]

Yes something like the second would work if the value of Metric_date is a macro variable. The macro processor can't use data set variables.

I would suggest one of two changes depending on where those macro variables for the date comparisons come from.

If you are supplying them as an option manually, or are calculated in a previous step , place them as Parameters to the macro

or calculate them inside the macro.

There are few things harder to debug then macro values that just "appear" in the middle of code with out a known source. Someone may attempt to modify such a macro for another purpose but may not have those macro variables available, or even worse, have the macro variable but with a different value range then expected.

[LL5]

Thanks Ballardw. I got the idea, so I added one step inside the macro but got the error saying statment as not valid.  See below - 

I agreed with you that this method is not ideally, but I want to explore a little bit more. Thanks for your advices. 

%MACRO LT ;

PROC SQL;
SELECT MAX(METRIC_DATE) INTO: D
FROM SQL.TABLE;
QUIT;

%IF METRIC_DATE EQ &SASDATEB OR %SYSEVALF(METRIC_DATE LT &SASDATE12) %THEN %DO;

PROC SQL;

DELETE FROM SQL.TABLE; /* MASTER TABLE*/

%END;

%ELSE %DO;

INSERT INTO SQL.TABLE/* MASTER TABLE*/

SELECT * FROM SAS.TABLE; /*NEW TABLE*/

%END;

QUIT;

%MEND;

45 INSERT INTO SQL.TABLE SELECT * FROM SAS.TABLE;
______
180

ERROR 180-322: Statement is not valid or it is used out of proper order.

[ballardw]

---

@LL5 wrote:

Thanks Ballardw. I got the idea, so I added one step inside the macro but got the error saying statment as not valid.  See below - 

I agreed with you that this method is not ideally, but I want to explore a little bit more. Thanks for your advices. 

%MACRO LT ;

PROC SQL;
SELECT MAX(METRIC_DATE) INTO: D
FROM SQL.TABLE;
QUIT;

%IF METRIC_DATE EQ &SASDATEB OR %SYSEVALF(METRIC_DATE LT &SASDATE12) %THEN %DO;

PROC SQL;

DELETE FROM SQL.TABLE; /* MASTER TABLE*/

%END;

%ELSE %DO;

INSERT INTO SQL.TABLE/* MASTER TABLE*/

SELECT * FROM SAS.TABLE; /*NEW TABLE*/

%END;

QUIT;

%MEND;

45 INSERT INTO SQL.TABLE SELECT * FROM SAS.TABLE;
______
180

ERROR 180-322: Statement is not valid or it is used out of proper order.

---

If you want to conditionally modify statements within a procedure then the Procedure start has to come before the condtion:

something like:

PROC SQL;
   %IF METRIC_DATE EQ &SASDATEB OR %SYSEVALF(METRIC_DATE LT &SASDATE12) %THEN %DO;
      DELETE FROM SQL.TABLE; /* MASTER TABLE*/
   %END;
 
   %ELSE %DO;
      INSERT INTO SQL.TABLE/* MASTER TABLE*/
      SELECT * FROM SAS.TABLE; /*NEW TABLE*/
   %END;
Quit;

Your code did not have the Proc SQL; start with the second condition. Your posted code still does not use the correct comparison though. The red highlighted text is going to be compared not anything that resembles a date value.

OR wrap entire Proc steps inside %do/%end blocks:

%IF METRIC_DATE EQ &SASDATEB OR %SYSEVALF(METRIC_DATE LT &SASDATE12) %THEN %DO;
   PROC SQL;
      DELETE FROM SQL.TABLE; /* MASTER TABLE*/
   quit;
%END;

%ELSE %DO;
   Proc sql;
      INSERT INTO SQL.TABLE/* MASTER TABLE*/
      SELECT * FROM SAS.TABLE; /*NEW TABLE*/
   Quit;
%END;

Please post code and log entries into a code box opened on the forum with the </> icon to preserve formatting. For Log error messages copy the entire proc step with errors from the log and paste into the code box. For debugging macros you will want to set OPTIONS MPRINT; to see the code actually generated by your macro. You may need SYMBOLGEN and MLOGIC options as well if the problem seems to be with the values of constructed variables or logic evaluation.

I notice that you create a macro variable D but don't use that in the code you have shared.

[Tom · Accepted solution]

You are still comparing the letters MET... to the value of the macro variable.  Did you mean to compare to the value of the macro variable D instead?  If you are going to compare date literals you need to use %SYSEVALF(). Normally the macro processor will just use %EVAL() to evaluate the conditions and that does not understand date literals, they look like strings to %EVAL.

%IF %sysevalf( (&d EQ &SASDATEB) OR (&d LT &SASDATE12)) %THEN %DO;

Do you really want to delete ALL of the observations when the %IF condition is true?  Before you moved into macro code you had a WHERE condition on the DELETE statement.

You cannot run the INSERT statement outside of a PROC SQL step.

[LL5]

Thanks Tom and Ballardw. This works now- see below codes and log (still haven't figure out how to put them in a code box?).

I realized I had two problems: 1) I should use %sysevalf to evaulate and compare date literals. 2) Since my current dataset has all the rows that meet the first %if condtion, so everything are being deleted (and this is the desired result). In this case, the %else never happen. In order to fix this, I put the insert command right after the delete command without the %else. According to the log, i got the expected result.

Thanks again for all the advics and suggestions.  

%MACRO LT;
PROC SQL;
SELECT MAX(METRIC_DATE) INTO: D
FROM SQL.TABLE;
QUIT;

PROC SQL;
%IF %sysevalf( (&D EQ &SASDATEB) OR (&D LT &SASDATE12)) %THEN %DO;
DELETE FROM SQL.TABLE;
INSERT INTO SQL.TABLE /* MASTER TABLE*/
SELECT * FROM SAS.TABLE; /*NEW TABLE*/
%END;
QUIT;
%MEND;

%LT ;

NOTE: 371655 rows were deleted from SQL.TABLE.

NOTE: 200 rows were inserted into SQL.TABLE.

NOTE: PROCEDURE SQL used (Total process time):
real time 0.54 seconds
cpu time 0.32 seconds

[Tom]

What is it that you want to TEST in your %IF statement?  So you posted this line:

%IF METRIC_DATE EQ &SASDATEB OR %SYSEVALF(METRIC_DATE LT &SASDATE12) %THEN %DO;

So if the macro variable SASDATE12 has a value like '01NOV2019'D  then you are testing if the character M is less than the character single quote, which is always going to be true.

So what date is it that you want to compare the value '01NOV2019'D ?  Where does that date come from?