topic Re: Help working with dates in New SAS User

Help working with dates

jorquec — Tue, 09 Jul 2019 10:04:55 GMT

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

Re: Help working with dates

ballardw — Tue, 09 Jul 2019 15:50:51 GMT

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

Re: Help working with dates

jorquec — Wed, 10 Jul 2019 10:13:42 GMT

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;

Re: Help working with dates

Tom — Wed, 10 Jul 2019 13:38:49 GMT

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)