Is there a way to nest a sql statement in a SAS IF/THEN/DO function?

user_15955206 0
Viewed 32

So I have a SAS job that is scheduled to run and update a table regularly. I'm trying to add a functionality that drops the oldest month from the table when it is updating on the first day of a new month. Right now it looks like this:

PROC sql;
create table DropOldMonth as
select *
from ret.ServiceGFR_Impact;


create table DropOldMonth2 as
select *
from DropOldMonth
where date <> 'Jan 2020';

data _null_;
IF FirstDayofMonth = &todaysDate THEN DO;
    proc sql;
    drop table ret.ServiceGFR_Impact;
    
    data ret.ServiceGFR_Impact;
    set work.DropOldMonth2;
END;
run;

But I get this error:

ERROR 117-185: There was 1 unclosed DO block.

right below the Proc sql statement. I assume it's because there's a proc sql statement before and END statement to the DO function. However I need it to drop that table when that IF condition is true.

sas sql
1 Answers

You may want to edit it in place instead of dropping it:

data ret.ServiceGFR_Impact;
  set ret.ServiceGFR_Impact;
  where date <> 'Jan 2020';
run;

make sure you have a backup before running it

user_138783250
Related