topic Re: Proc SQL with IF in SAS Programming

Proc SQL with IF

wrosario — Mon, 12 Jan 2015 17:52:13 GMT

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;

Re: Proc SQL with IF

saidi — Mon, 12 Jan 2015 18:23:00 GMT

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;

;

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

Re: Proc SQL with IF

Astounding — Mon, 12 Jan 2015 18:38:14 GMT

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.

Re: Proc SQL with IF

saidi — Mon, 12 Jan 2015 19:09:53 GMT

Thank you...