Proc SQL with IF

[wrosario]

Friends, I need to create the situation below, do you have any solutions?

PROC SQL PRINT;

  SELECT COUNT(1) INTO :CONT FROM SIAR_CGA.CONTROLE_CARGA_JOBS WHERE COD_PROCESSO_JOB = 5;

    CREATE TABLE SIAR_CGA.MYTABLE

           (CONTADOR NUM);

       IF &CONT=0 THEN

            INSERT INTO SIAR_CGA.MYTABLE(CONTADOR) SELECT COUNT(1) FROM SIAR_CGA.CONTROLE_CARGA_JOBS;

       ELSE

            INSERT INTO SIAR_CGA.MYTABLE(CONTADOR) SELECT COUNT(1) FROM SIAR_CGA.CONTROLE_CARGA_JOBS WHERE COD_PROCESSO_JOB = 5;

QUIT;

[saidi]

Probable reason might be

1. Macro variable is created after compilation only (After Quit statement)

2. If else condition doesn't work in SQL/Open code.

Try this way.

DATA CONTROLE_CARGA_JOBS;

input COD_PROCESSO_JOB;

cards;

5

4

4

;

run;

%macro test;

PROC SQL PRINT;

  SELECT COUNT(1) INTO :CONT2 FROM CONTROLE_CARGA_JOBS

WHERE COD_PROCESSO_JOB = 5;

QUIT;

Proc sql;

    CREATE TABLE MYTABLE

           (CONTADOR NUM);

quit;

%IF &CONT2=0 %THEN %do;

proc sql;

INSERT INTO MYTABLE(CONTADOR) SELECT COUNT(1) FROM CONTROLE_CARGA_JOBS;

quit;

%end;

%ELSE %do;

proc sql;  

INSERT INTO MYTABLE(CONTADOR) SELECT COUNT(1) FROM CONTROLE_CARGA_JOBS WHERE COD_PROCESSO_JOB = 5;

Quit;

%end;

%mend test;

%test

[Astounding]

On the one hand, this solution should work ... at least if the original posted SQL code is correct.  On the other hand, a lot of it can be removed.  Compare with:

%macro test;

PROC SQL PRINT;

  SELECT COUNT(1) INTO :CONT2 FROM CONTROLE_CARGA_JOBS

WHERE COD_PROCESSO_JOB = 5;

    CREATE TABLE MYTABLE

           (CONTADOR NUM);

INSERT INTO MYTABLE(CONTADOR) SELECT COUNT(1) FROM CONTROLE_CARGA_JOBS

%if &CONT2 ne 0 %then  WHERE COD_PROCESSO_JOB = 5;

;

Quit;

%mend test;

%test

Note that SQL statements execute immediately.  So the SELECT statement creates &CONT2 in time for the next statement to use it.

Also note, the lone semicolon on a line by itself ends the INSERT statement.

[saidi]

Thank you...