Help working with dates

[jorquec]

Hi all, 

Could someone please help me with this code, I dont have much experience with date  and macros.

I need to create a dataset based on a date condition, I mean look into table A and see if the month of the maximum date in this table is equal to current month or previous month, if yes  then create a table with the max date found in table A.

Example Table A

Month_end_dt   ID

30/04/2019         1

30/05/2019         2

30/06/ 2019       3

So as we are on July/19  and max Month_end_date is 30/06/2019 on table A then  the trigger is fine ( current month less 1), so start the process to create a table B with the max Month_end_date.

I have tried this but is not working properly:

proc sql noprint ;
connect to teradata
(user=&teradata_user. password=&teradata_pwd. server = 'edwprod' database = 'nuc_pl_user_view');
Create table work.statuspayg as
(
select max_month

from (select max(month((month_end_dt)) as max_month from table A)
)
;

quit;
%macro test;
%if max_month<= current_month or current_month - 1 %then %do;
data jorquec.STATUSPAYG_final;
set work.statuspayg;
run;
%end;
%mend

[ballardw]

---

@jorquec wrote:

Hi all, 

Could someone please help me with this code, I dont have much experience with date  and macros.

I need to create a dataset based on a date condition, I mean look into table A and see if the month of the maximum date in this table is equal to current month or previous month, if yes  then create a table with the max date found in table A.

Example Table A

Month_end_dt   ID

30/04/2019         1

30/05/2019         2

30/06/ 2019       3

So as we are on July/19  and max Month_end_date is 30/06/2019 on table A then  the trigger is fine ( current month less 1), so start the process to create a table B with the max Month_end_date.

I have tried this but is not working properly:

proc sql noprint ;
connect to teradata
(user=&teradata_user. password=&teradata_pwd. server = 'edwprod' database = 'nuc_pl_user_view');
Create table work.statuspayg as
(
select max_month

from (select max(month((month_end_dt)) as max_month from table A)
)
;

quit;
%macro test;
%if max_month<= current_month or current_month - 1 %then %do;
data jorquec.STATUSPAYG_final;
set work.statuspayg;
run;
%end;
%mend

---

Can you show an example that works without any macro variables or macro code?

If we see that then we can suggest changes.

As a minimum your macro test does not have any value for the "max_month" or "current_month". It is comparing literal text and max_month will always be greater than current_month as "m" comes after "c" (is greater than) when comparing text values.

Note that it is a very good idea to actually pass parameters to a macro and not have values just "appear" in the middle of code as it becomes very hard to determine where the value may have originated.

[jorquec]

Hi

I have tried this below and it works, the problem is that Today is not a good reference for my process. I would like something that check if max month_end_date on Table A is equal to current month or previous month than create a table B.

I don't have any idea how to create this logical. Any help will be great.

proc sql noprint ;
connect to teradata
(user=&teradata_user. password=&teradata_pwd. server = 'edwprod' database = 'nuc_pl_user_view');
Create table STATUSPAYG as(
select max_dt = today()
into :today trimmed
from (select max(month_end_dt) as max_dt from table A)
)
;

quit;
%macro test;
%if &today %then %do;
data tableB_final;
set STATUSPAYG;
run;
%end;
%mend
quit;

[Tom]

I think you are asking if the max date is in the current month?

You might want to use the INTNX() function to adjust the dates to the beginning (or end) of a month.

So perhaps

intnx('month',max_dt,0) = intnx('month',today(),0)