BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Bottoni
Fluorite | Level 6

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();
1 ACCEPTED SOLUTION

Accepted Solutions
Shmuel
Garnet | Level 18

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

 

View solution in original post

3 REPLIES 3
Shmuel
Garnet | Level 18

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

 

Bottoni
Fluorite | Level 6

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). 

ChrisNZ
Tourmaline | Level 20

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;

 

 

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
  • 4791 views
  • 1 like
  • 3 in conversation