I'm trying to verify if a database is refreshed in businessday -2. I have used the following code, but when I run the program it always stays in sleep mode! What can I do to solve this problem?
%DO %UNTIL(&DIA_MENOS_2. = MAIOR_DIA);
MAIOR_DIA = MAX(DATA_FORMALIZACAO);
CALL SLEEP(60);
%END;
FULL CODE:
%MACRO VERIFICAR_DIA();
DATA DUTIL_MENOS_2;
ATTRIB DIA_MENOS_2 FORMAT=DDMMYY10. INFORMAT=DDMMYY10. LENGTH=8.;
DIA_MENOS_2 = INTNX('WEEKDAY',TODAY(),-2);
RUN;
DATA _null_;
SET WORK.DUTIL_MENOS_2;
CALL SYMPUT("DIA_MENOS_2", DIA_MENOS_2);
RUN;
DATA TESTE;
SET BASE_X;
ATTRIB MAIOR_DIA FORMAT=DDMMYY10. INFORMAT=DDMMYY10. LENGTH=8.;
MAIOR_DIA = MAX(DATA_FORMALIZACAO);
%DO %UNTIL(&DIA_MENOS_2. = MAIOR_DIA);
MAIOR_DIA = MAX(DATA_FORMALIZACAO);
CALL SLEEP(60);
%END;
RUN;
%MEND;
%VERIFICAR_DIA();
Few remarks to your code:
1) You can join the first two steps into one:
DATA DUTIL_MENOS_2;
ATTRIB DIA_MENOS_2 FORMAT=DDMMYY10.
INFORMAT=DDMMYY10. LENGTH=8.;
DIA_MENOS_2 = INTNX('WEEKDAY',TODAY(),-2);
CALL SYMPUT("DIA_MENOS_2", DIA_MENOS_2);
RUN;
2) What did you mean by next line:
MAIOR_DIA = MAX(DATA_FORMALIZACAO);
sas is working row by row. Redaing first row , if it isn't the date - you enter the sleep mode.
Besides you have a mismach of datastep statement (do until) with macro code (%do %until).
You can't use it as you have done.
3) to calculate max value of a column, you can use sql:
proc sql;
select MAX(DATA_FORMALIZACAO) into: MAIOR_DIA
from BASE_X;
quit;
then you can check in a separate datastep if to continue or sleep.
4) Did you mean to sleap 60 seconds, and then what do you want to do ?
Iguess you want next code:
%MACRO VERIFICAR_DIA(); DATA DUTIL_MENOS_2; ATTRIB DIA_MENOS_2 FORMAT=DDMMYY10. INFORMAT=DDMMYY10. LENGTH=8.; DIA_MENOS_2 = INTNX('WEEKDAY',TODAY(),-2); CALL SYMPUT("DIA_MENOS_2", DIA_MENOS_2); RUN;
proc sql; select MAX(DATA_FORMALIZACAO) into: MAIOR_DIA from BASE_X;
quit;
data _NULL_;
dia_menos_2 = symget("DIA_MENOS_2");
MAIOR_DIA = symget("MAIOR_DIA");
if maior_dia = dia_menos_2 then call symput('SLEEP','NO');
else call symput('SLEEP','YES');
run;
%if &sleep = NO %then %do;DATA TESTE;
SET BASE_X;
ATTRIB MAIOR_DIA FORMAT=DDMMYY10. INFORMAT=DDMMYY10. LENGTH=8.;
MAIOR_DIA =symget("MAIOR_DIA");
run;
%end; %else %do;
data _NULL_;
CALL SLEEP(60);
run;
%END;
%MEND;
%VERIFICAR_DIA();
5) Have you considered a situation where
maior_dia > dia_menos_2
Few remarks to your code:
1) You can join the first two steps into one:
DATA DUTIL_MENOS_2;
ATTRIB DIA_MENOS_2 FORMAT=DDMMYY10.
INFORMAT=DDMMYY10. LENGTH=8.;
DIA_MENOS_2 = INTNX('WEEKDAY',TODAY(),-2);
CALL SYMPUT("DIA_MENOS_2", DIA_MENOS_2);
RUN;
2) What did you mean by next line:
MAIOR_DIA = MAX(DATA_FORMALIZACAO);
sas is working row by row. Redaing first row , if it isn't the date - you enter the sleep mode.
Besides you have a mismach of datastep statement (do until) with macro code (%do %until).
You can't use it as you have done.
3) to calculate max value of a column, you can use sql:
proc sql;
select MAX(DATA_FORMALIZACAO) into: MAIOR_DIA
from BASE_X;
quit;
then you can check in a separate datastep if to continue or sleep.
4) Did you mean to sleap 60 seconds, and then what do you want to do ?
Iguess you want next code:
%MACRO VERIFICAR_DIA(); DATA DUTIL_MENOS_2; ATTRIB DIA_MENOS_2 FORMAT=DDMMYY10. INFORMAT=DDMMYY10. LENGTH=8.; DIA_MENOS_2 = INTNX('WEEKDAY',TODAY(),-2); CALL SYMPUT("DIA_MENOS_2", DIA_MENOS_2); RUN;
proc sql; select MAX(DATA_FORMALIZACAO) into: MAIOR_DIA from BASE_X;
quit;
data _NULL_;
dia_menos_2 = symget("DIA_MENOS_2");
MAIOR_DIA = symget("MAIOR_DIA");
if maior_dia = dia_menos_2 then call symput('SLEEP','NO');
else call symput('SLEEP','YES');
run;
%if &sleep = NO %then %do;DATA TESTE;
SET BASE_X;
ATTRIB MAIOR_DIA FORMAT=DDMMYY10. INFORMAT=DDMMYY10. LENGTH=8.;
MAIOR_DIA =symget("MAIOR_DIA");
run;
%end; %else %do;
data _NULL_;
CALL SLEEP(60);
run;
%END;
%MEND;
%VERIFICAR_DIA();
5) Have you considered a situation where
maior_dia > dia_menos_2
Thank you very much Shmuel.
"Did you mean to sleap 60 seconds, and then what do you want to do ?" After that, I want verify again if the data has refreshed and run a "query". Otherwise I call the funcion sleep again.
"Have you considered a situation where"
maior_dia > dia_menos_2
Yes, I do. The data refreshes each two days, because of that maior_dia (last day that appears in the data) will never be greater than dia_menos_2 (today - 2).
Much confusion this code has!
Not too sure what you are attempting to do, but at least these 2 things raise questions:
1-You cannot use %do %until and test a dataset variable.
The macro statements will be executed before any data is read.
2-Note that:
MAIOR_DIA = MAX(DATA_FORMALIZACAO);
does nothing more than
MAIOR_DIA = DATA_FORMALIZACAO;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.