topic Re: Max date (DATA SET) in SAS Programming

Max date (DATA SET)

Bottoni — Thu, 09 Mar 2017 18:53:01 GMT

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

Re: Max date (DATA SET)

Shmuel — Thu, 09 Mar 2017 20:12:23 GMT

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

Re: Max date (DATA SET)

ChrisNZ — Thu, 09 Mar 2017 20:13:29 GMT

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;

Re: Max date (DATA SET)

Bottoni — Thu, 09 Mar 2017 20:57:15 GMT

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