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;
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
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.
Thank you...
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.