DATA Step, Macro, Functions and more

Proc SQL with IF

Reply
Contributor
Posts: 21

Proc SQL with IF

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;

Occasional Contributor
Posts: 10

Re: Proc SQL with IF

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

Super User
Posts: 5,092

Re: Proc SQL with IF

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.

Occasional Contributor
Posts: 10

Re: Proc SQL with IF

Thank you...

Ask a Question
Discussion stats
  • 3 replies
  • 448 views
  • 1 like
  • 3 in conversation