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...
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.