BookmarkSubscribeRSS Feed
wrosario
Calcite | Level 5

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;

3 REPLIES 3
saidi
Fluorite | Level 6

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
PROC Star

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
Fluorite | Level 6

Thank you...

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 1317 views
  • 1 like
  • 3 in conversation