Running an IF Else with a value from a table to execute another proc sql

[cyberjunkie]

Trying to figure out what the proper code to execute an if else from SAS EG with a value from a table.  If you see below I've commented out the sas date value and I want to replace it with a select statement from a table.  Basically I just want the execute to run when the today value -1 = the value in the table I created prior based on SASDate.  I've tried creating a query from the snapshot_date table and using the select sql syntax and replacing the value but it keeps giving me an ERROR 388-185: Expecting an arithmetic operator. 

---------------------------------------------------------------------------------------------------

PROC SQL;
CREATE TABLE SNAPSHOT_DATE AS
SELECT PUT(DATEPART(MAX(AUDIT_BATCH_TS))-1,yymmddd10.) as 'Snapshot Date'n,
today() as RunDate,
DATEPART(MAX(AUDIT_BATCH_TS))-1 as SASDate FROM Ora_PSA.CC_ACTIVITY;

----------------------------------------------------------------------------------------------------------------------

data _null_;

if (today()) =/*22256*/ then call execute(

"PROC SQL;

CREATE TABLE DIM_ASSIGNQUEUE AS
SELECT DISTINCT Q1.ID as 'QUEUEID'n,
Q1.NAME as 'Queue Name'n,
Q1.DESCRIPTION as 'Queue Description'n,
GROUPID FROM Ora_PSA.CC_ASSIGNQUEUE Q1 INNER JOIN (SELECT ID, MAX(AUDIT_BATCH_TS) AS MAXTS FROM Ora_PSA.CC_ASSIGNQUEUE GROUP BY ID) MAXQ ON MAXQ.ID = Q1.ID AND MAXQ.MAXTS = Q1.AUDIT_BATCH_TS;

QUIT;"

);

run;

[Tom]

I cannot picture what you want to do. 

Can you post example data from both tables? Please post as simple data steps to recreate the data.

For your posted input data show what code you want to run and what output it should produce.  Please show clearly what part of the code you want to run with these selected value(s) depends on (or changes) based on that value was selected from the other table.

[cyberjunkie]

Basically I want to be able to use the value from this table attached to populate the highlighted text in RED below.  I'd like a dynamic value based on the results of my first PROC SQL query to drive the next step.  If the snapshot date matches then it will continue to create the next table, otherwise it will not.  Hope that explains what I'm trying to do.  

data _null_;

if (today()) =22256 then call execute(

"PROC SQL;

CREATE TABLE DIM_ASSIGNQUEUE AS
SELECT DISTINCT Q1.ID as 'QUEUEID'n,
Q1.NAME as 'Queue Name'n,
Q1.DESCRIPTION as 'Queue Description'n,
GROUPID FROM Ora_PSA.CC_ASSIGNQUEUE Q1 INNER JOIN (SELECT ID, MAX(AUDIT_BATCH_TS) AS MAXTS FROM Ora_PSA.CC_ASSIGNQUEUE GROUP BY ID) MAXQ ON MAXQ.ID = Q1.ID AND MAXQ.MAXTS = Q1.AUDIT_BATCH_TS;

QUIT;"

);

run;

[Tom · Accepted solution]

So to get the MAX() date from your first query use code like this:

proc sql noprint;
SELECT DATEPART(MAX(AUDIT_BATCH_TS))-1, today()
  into :snapshot_date trimmed, :rundate trimmed
  from Ora_PSA.CC_ACTIVITY
;
quit;

That will create two macro variables with raw SAS date values.

Then to conditionally run the other step you could use %DO loop.

%if &rundate = &snapshot_date %then %do;
PROC SQL;
CREATE TABLE DIM_ASSIGNQUEUE AS
  SELECT DISTINCT Q1.ID as 'QUEUEID'n
       , Q1.NAME as 'Queue Name'n
       , Q1.DESCRIPTION as 'Queue Description'n
       , GROUPID 
  FROM Ora_PSA.CC_ASSIGNQUEUE Q1
  INNER JOIN 
    (SELECT ID, MAX(AUDIT_BATCH_TS) AS MAXTS 
     FROM Ora_PSA.CC_ASSIGNQUEUE 
     GROUP BY ID
    ) MAXQ
  ON MAXQ.ID = Q1.ID AND MAXQ.MAXTS = Q1.AUDIT_BATCH_TS
;
QUIT;
%end;

If you are using an old version of SAS where the open code %IF/%THEN/%DO is not allowed then you could use your data step instead.

if &run_date= &snapshot_date then ...

Not sure why you are making variable names with spaces in them.  It is much easier to program if the names follow normal conventions.  You can always attach labels to the variables if you want your reports to default to nice text for headers.

[cyberjunkie]

OMG that worked.  Thank thank you!!!  I just reversed the macro to:

%if &snapshot_date = &rundate-1 %then %do; 

[cyberjunkie]