topic Re: Macro running on month condition in SAS Procedures

Macro running on month condition

sasnewbiexx — Mon, 06 May 2019 14:28:29 GMT

Hello everyone,

Basically, I'm trying to create the following macro:

%Macro CheckDate;

%if &month= this month %then %do;

Proc Sql;
Create table Work.Test1 as
Select A.*
From Work.Table1
;
Quit;
%End;

%Else %do;

signoff

%End;
%Mend;
%CheckDate;

I am trying to get my month macrovariable to be the month part for the first observation from a datetime column from another table (lets say Work.Table2). Any ideas how can I do that?

Re: Macro running on month condition

Astounding — Mon, 06 May 2019 14:41:20 GMT

Here's the sort of information you will need to supply to approach this problem.

What is in that datetime value from the other table? Is it a character string? Is it a SAS numeric datetime?

How do you intend to populate "this month"? Is it something you feed in as a parameter? (If so, is it a number from 1 to 12, or a three-letter abbreviation, or a full word?) Is it the month during which the program runs?

Is it always sufficient to check just the month, so you will never need to check the year as well?

Re: Macro running on month condition

Reeza — Mon, 06 May 2019 14:55:24 GMT

TODAY() returns todays date

MONTH() retrieves the month from a SAS date.

To use functions within macro logic, wrap them in %SYSFUNC().

Not really sure where the other month value is coming from.

%if %sysfunc(today(), month.) = 5 %then %do;

@sasnewbiexx wrote:

Hello everyone,

Basically, I'm trying to create the following macro:

%Macro CheckDate;

%if &month= this month %then %do;

Proc Sql;
Create table Work.Test1 as
Select A.*
From Work.Table1
;
Quit;

%End;

%Else %do;

signoff

%End;
%Mend;
%CheckDate;

I am trying to get my month macrovariable to be the month part for the first observation from a datetime column from another table (lets say Work.Table2). Any ideas how can I do that?

Re: Macro running on month condition

sasnewbiexx — Tue, 07 May 2019 07:12:06 GMT

@Astounding

The datetime format from the other table is DATETIME22.3 (11APR2019:10:27:15.780).

My apologizes, when I say "this month", I meant to say the current month's value. I intend to take from SAS, but I'm not sure how in this case given the format of the date value from the other table.

The table that I take the date from gets updated once a month, but the DBA told me that it is not on a prefixed date so it can be in the beginning of the month or the end. Therefore, my idea is to make my program execute only if the month value is different than the current month's value and just skip to signoff if the month values match.

I wouldn't need to check the year since the data is updated every month and all the values in the datetime column I take are from the same day.

Re: Macro running on month condition

Astounding — Tue, 07 May 2019 13:29:51 GMT

Since you need to pull data from a separate data set to get the comparison datetime value, I would recommend using a DATA step and doing all the heavy lifting in that DATA step. For example:

%macro CheckDate;
   
   %local proceed;
   %let proceed=N;
   data _null_;
      set other_table (keep=last_update_datetime);
      if month(today()) ne month(datepart(last_update_datetime)) then
      call symput('proceed', 'Y');
      stop;
   run;
   %if &proceed=Y %then %do;
      ...
   %end;
    %else %do;
         * signoff logic;
    %end;

%mend CheckDate;

Of course, I had to invent names for your data sets and variables. And I assumed that your datetime source is actually a numeric SAS datetime, not a character string. All of that can be tweaked if necessary.

Re: Macro running on month condition

sasnewbiexx — Wed, 08 May 2019 14:01:46 GMT

Thank you so much, this was exactly what I was looking for! Cheers!