DATA Step, Macro, Functions and more

Max date (DATA SET)

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

Max date (DATA SET)

[ Edited ]

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();

Accepted Solutions
Solution
‎03-10-2017 11:35 AM
Trusted Advisor
Posts: 1,378

Re: Max date (DATA SET)

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


All Replies
Solution
‎03-10-2017 11:35 AM
Trusted Advisor
Posts: 1,378

Re: Max date (DATA SET)

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

 

Occasional Contributor
Posts: 9

Re: Max date (DATA SET)

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

PROC Star
Posts: 1,561

Re: Max date (DATA SET)

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;

 

 

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

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