topic Re: macro compare two dates in marcro - help in New SAS User

macro compare two dates in marcro - help

ph10 — Thu, 09 Jan 2020 18:36:56 GMT

Hello, i would like a hand to resolve this macro error:

Macro:

---------------------------------------------------------------------------------------------------------------------------------------

%MACRO datas();
%GLOBAL DT_sis dt_mvt DtDB2 err nr_unco_ini nr_unco_final nr_unco_tmp;

DATA _NUL_;
CALL SYMPUT("nr_unco_final", put(&ano, z4.) || put('9999999999999', 13.));
RUN;

/* VERIFICA O DIA DA SEMANA PARA INICIAR OS PROCESSO DE VALIDAÇÃO DE DATAS DO MOVIMENTO, SE FOR SEGUNDA, DEVERÁ SER D-3*/
DATA _NUL_;
IF &SYSDAY = Monday THEN
CALL SYMPUT("dt_sis", PUT(INTNX("DAY",TODAY(),-3), DATE10.));
ELSE
CALL SYMPUT("dt_sis", PUT(INTNX("DAY",TODAY(),-1), DATE10.));
RUN;

/*************************************/

/*IDENTIFICA A ÚLTIMA DATA DE MOVIMENTO EM */
PROC SQL;
CREATE TABLE DATA AS
SELECT MAX(DT_MVT) AS DT_REF FORMAT DATE10. FROM tabela de origem
;
QUIT;
/*************************************/

/* Get the last movement in the variable dt_mvt*/
DATA _NULL_;
SET DATA;
FORMAT dt_mvt DATE10.;
INFORMAT dt_mvt DATE10.;
CALL SYMPUT("dt_mvt",PUT(DT_REF,DATE10.));
RUN;
/*************************************/
DATA _NULL_;

call symput("DtDB2", COMPRESS("'" || PUT(day("&dt_mvt"d),Z2.) || '.'
|| PUT(month("&dt_mvt"d),Z2.) || '.'
|| PUT(Year("&DT_MVT"d), Z4.) || "'"));
RUN;
%mend;
%datas;

data _null_;
call execute('%datas;');
If &dt_sis NE &dt_mvt then do;
%put 'EVG não atualizado.';
end;
else do;
put 'EVG atualizado.';
stop;
end;
run;

----------------------------------------------------------------------------------------------------------------------------------------------

mensagem de erro:

26 data _null_;
27 call execute('%datas;');
SYMBOLGEN: Macro variable DT_SIS resolves to 06JAN2020
SYMBOLGEN: Macro variable DT_MVT resolves to 08JAN2020
28 If &dt_sis NE &dt_mvt then do;
NOTE: Line generated by the macro variable "DT_SIS".
28 06JAN2020
_______
22
NOTE: Line generated by the macro variable "DT_MVT".
28 08JAN2020
_______
388
76
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, -, /, <, <=, <>, =, >, ><, >=, AND, EQ, GE, GT, IN,
LE, LT, MAX, MIN, NE, NG, NL, NOTIN, OR, ^=, |, ||, ~=.

ERROR 388-185: Expecting an arithmetic operator.

ERROR 76-322: Syntax error, statement will be ignored.

29 %put 'EVG não atualizado.';
'EVG não atualizado.'
30 end;
___
161
ERROR 161-185: No matching DO/SELECT statement.

31 else do;
32 put 'EVG atualizado.';
2 The SAS System 15:06 Thursday, January 9, 2020

33 stop;
34 end;
35 run;

------------------------------------------------------------------------------------------------------------------

Re: macro compare two dates in marcro - help

Tom — Thu, 09 Jan 2020 18:57:26 GMT

The way to reference a date literal in SAS code is a quoted string that the DATE informat can understand with the letter D at the end.

If "06JAN2020"d NE "08JAN2020"d  then do;
  ...
end;

To get your macro variables as constructed to generate code like that you need add the quotes and D into your code.

If "&dt_sis"d NE "&dt_mvt"d then do;

Note that your use of macro and SAS code is very messed up. For example you have replaced the ... in my example above with the macro statement %PUT. When SAS runs your code it will evaluate the macro statement while is is reading the text and trying to compiler the data set. So the %PUT will always run before the data step even starts running. So whether the condition in the IF statement is true or false will not matter. If instead you used a PUT statement it might make more sense. Also using CALL EXECUTE to try to run the macro %DATAS doesn't make any sense.

%datas;

data _null_;
  if "&dt_sis"d <= "&dt_mvt"d then do;
    put 'EVG não atualizado.';
  end;
  else do;
    put 'EVG atualizado.';
  end;
  stop;
run;

If you really do need to use those ddMMMyyyy strings in macro logic then you need to use %SYSEVALF() as the %EVAL() that macro processor normally uses to evaluate conditions in %IF statements only understands text comparisons and integer arithmetic so it does not understand date literals.

%datas;

%if %sysevalf("&dt_sis"d <= "&dt_mvt"d) %then %do;
    %put EVG não atualizado.;
%end;
%else %do;
    %put EVG atualizado.;
%end;

You might get better results not making the macro variables using any date format at all. Instead just store the string of digits that represent the number of days that SAS uses to store those dates. So don't use the PUT() function when generating the value to put into the macro variable. Then your code that just compared the value of the two macro variables will work in either SAS logic or macro logic.

Re: macro compare two dates in marcro - help

ph10 — Thu, 09 Jan 2020 19:17:30 GMT

Thank's Tom, It is working now.

You also said that using the call execute, it is not the better way to do the work.

I am trying to create a process in SAS to create a table that will be used in a spotfire graphic. I would like to make a automatic way to do it. Do you have a suggestion to write it?

Re: macro compare two dates in marcro - help

Tom — Thu, 09 Jan 2020 19:26:05 GMT

CALL EXECUTE() is useful if the code you want to pass needs to be built from data you have in a SAS dataset. You might also use it conditionally generate code since you could potentially avoid the need to create a macro (or at least another macro) so that you can run macro logic. Instead you could just use data step logic to decide which CALL EXECUTE() statements execute. Remember that the code is generated by the CALL EXECUTE() statement runs after the data step that is running the CALL EXECUTE() statement.
If you want more detailed help start a new question with the details of what you are trying to do and what question(s) you are having trouble with.

Re: macro compare two dates in marcro - help

PaigeMiller — Thu, 09 Jan 2020 19:33:54 GMT

You make your coding so much harder by trying to turn the SAS dates (which are integers representing number of days since 1/1/1960) like 21789 into human readable text strings like 08SEP19. This part of code simply isn't necessary:

DATA _NULL_;

call symput("DtDB2", COMPRESS("'" || PUT(day("&dt_mvt"d),Z2.) || '.'
|| PUT(month("&dt_mvt"d),Z2.) || '.'
|| PUT(Year("&DT_MVT"d), Z4.) || "'"));
RUN;

There are many other examples in your code.

Leave dates as integers, unformatted. This simplifies everything and reduces the likelihood of errors. You would only want to format macro variables if they were needed for titles or labels, where humans need to see a recognizable date.

Re: macro compare two dates in marcro - help

ph10 — Thu, 09 Jan 2020 20:05:25 GMT

I used that code because if the the date of the sistem and the last the of the table are different, i will start a process to update that table and the source of that is a DB2 table, that's why i am transforming that date. Do you suggest a better way, Tom?

Re: macro compare two dates in marcro - help

Tom — Thu, 09 Jan 2020 20:18:45 GMT

@ph10 wrote:

I used that code because if the the date of the sistem and the last the of the table are different, i will start a process to update that table and the source of that is a DB2 table, that's why i am transforming that date. Do you suggest a better way, Tom?

The details matter a lot. No need to format the data values to compare them.

%let now=%sysfunc(date());
proc sql ;
  select max(rundate) into last_run from some_dataset ;
quit;

%if &rundate < &now %then %do;
.... code to update ...
%end;

If you are pushing date literals into DB2 then you need to know how to format them for us in your DB2 code. For example many database systems will allow you to use a date value in yyyy-mm-dd style inside of single quotes. So if I wanted to convert the date values in the macro variable LAST_RUN used in the above code into that form I might use code like this:

data _null_;
  call symputx('last_run_db2',quote(put(&last_run,yymmdd10.),"'"));
run;

So then I could use that macro variable in my DB2 code.

proc sql;
connect to db2 ..... ;
execute by db2
(  ... my DB2 code ... WHERE date > &last_run_db2 .... )
;
quit;

Re: macro compare two dates in marcro - help

ph10 — Thu, 09 Jan 2020 20:50:43 GMT

i am not tryaing to put the into DB2, but extracting from a db2 table. The whole process will write only8 SAS table, but i need to get the datas from DB2 tables, if it is not updated.