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