Formatting date variable and creating a macro variable

[donspaul]

Hello,

I need help with processing a date variable that I need to pass to a macro variable.  The date variable is in the following format 20MAR2025:00:00:00.000000 (DATETIME25.6).  I need help with accomplishing 2 things.  First, I need to reformat this date variable like so 2025-03-20 00:00:00.  Second, I need to pass "2025-03-20 00:00:00" into a macro variable. 

Thank you.

[PaigeMiller]

Most of the time, you do not want formatted macro variables. You want unformatted macro variables. See Maxim 28. (Yes, there are exceptions, so please tell us what you will do with this macro variable in the programming you are doing)

So, an unformatted macro variable can be obtained thusly:

data have;
    datetime='20MAR2025:00:00:00.000000'dt;
    call symputx('datetime',datetime);
run;

%put &=datetime; 

and it can be used unformatted for any mathematical calculations (such as INTNX or INTCK or others) and for any logical comparisons (such as if dt<&datetime where dt is a data set variable which contains datetime values).

[donspaul]

Hi,

Thank you for weighing in, but unfortunately, I am still having issues. 

I am trying to set up an automation that will work like this: (1) identify the maximum date in a data table (end date), (2) use INTNX to capture the 12-month prior date (start date), (3) pass the start-date and the end-date to 2 separate macro variables, and (4) set the start-date and end-date (macro variables) in a WHERE clause so it returns rows only within these 2-dates.

When I manually set the macro variable as follows, the query works just fine:

%let start_dt = '2020-12-31 00:00:00';
%let end_dt = '2024-01-01 00:00:00';

However, when I used the following code, the query is not returning any rows.  Because the only difference is in how the date fields are being passed to the query, I am suspecting the macro variable is not resolving properly.

PROC SQL;
SELECT END_DATE FORMAT=BEST32.
INTO: END_DATE
FROM DATA_DATE_END;
QUIT;

I am having the same issue when I used the code you had shared below. 

Please let me know if you have any further questions.  I don't know if this is material or not, but I am using Enterprise Guide to query a table in RedShift using an ODBC connection.  

Thank you.

[Tom]

So it sounds like you are using pass thru SQL so that you want to use the macro variable to generate REDSHIFT SQL syntax, not SAS syntax.

You can use the QUOTE() function to add those single quotes around the value.

1    data _null_;
2      dt = '20MAR2025:00:00:00.000000'dt;
3      call symputx('dtstring',quote(put(dt,dtdate9.)||' '||put(dt,tod8.),"'"));
4    run;

NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


5
6    %put &=dtstring;
DTSTRING='20MAR2025 00:00:00'

[PaigeMiller]

---

@donspaul wrote:

Hi,

Thank you for weighing in, but unfortunately, I am still having issues. 

I am trying to set up an automation that will work like this: (1) identify the maximum date in a data table (end date), (2) use INTNX to capture the 12-month prior date (start date), (3) pass the start-date and the end-date to 2 separate macro variables, and (4) set the start-date and end-date (macro variables) in a WHERE clause so it returns rows only within these 2-dates.

When I manually set the macro variable as follows, the query works just fine:

%let start_dt = '2020-12-31 00:00:00';
%let end_dt = '2024-01-01 00:00:00';

However, when I used the following code, the query is not returning any rows.  Because the only difference is in how the date fields are being passed to the query, I am suspecting the macro variable is not resolving properly.

PROC SQL;
SELECT END_DATE FORMAT=BEST32.
INTO: END_DATE
FROM DATA_DATE_END;
QUIT;

I am having the same issue when I used the code you had shared below. 

Please let me know if you have any further questions.  I don't know if this is material or not, but I am using Enterprise Guide to query a table in RedShift using an ODBC connection.  

Thank you.

---

Thank you for explaining, there's no way anyone can give valid advice without these details. You want &END_DATE to be unformatted. You want &START_DATE to be unformatted. Then everything works, assuming your date variable is NUMERIC date variable and has any format you want or even if it is unformatted.

PROC SQL;
    SELECT END_DATE FORMAT=BEST32.
    INTO: END_DATE
    FROM DATA_DATE_END;
QUIT;
/* Step 2 */
data _null_;
     set have;
     /* NOTE WE USE UNFORMATTED &END_DATE */
     start_date=intnx('dtmonth',&end_date,-12,'b');
     /* NOTE &START_DATE is unformatted */
     call symputx('start_date',start_date);
run;
/* Step 3 */
data want;
    /* NOTE VARIABLES CAN BE FORMATTED IN THE DATA SET */
    set yourbigdataset;
    /* NOTE THIS WORKS WITH UNFORMATTED MACRO VARIABLES */
    if datevariable = &start_date or date variable=datepart(&end_date) then output;
run;    

If datevariable is actually a datetime variable, we can modify the code to work with that as well.

[PaigeMiller]

I don't know if this is material or not, but I am using Enterprise Guide to query a table in RedShift using an ODBC connection.

So, yes, it depends on what you are doing and again, more details are needed. What date or datetime values do you need in this ODBC connection? If you were to type in a specific date, what would this WHERE statement look like? Please be exact.

[Tom]

---

@donspaul wrote:

Hello,

I need help with processing a date variable that I need to pass to a macro variable.  The date variable is in the following format 20MAR2025:00:00:00.000000 (DATETIME25.6).  I need help with accomplishing 2 things.  First, I need to reformat this date variable like so 2025-03-20 00:00:00.  Second, I need to pass "2025-03-20 00:00:00" into a macro variable. 

Thank you.

---

Do you mean you have NUMERIC  variable that has the DATETIME25.6 format attached to it so the values print in that character pattern you showed?  Or do you have CHARACTER variable that has strings that match the pattern that the DATETIME25.6 format will generate.

If you have a datetime value you want to generate a string like that into a macro variable you could generate it in two pieces.  Say your existing variable is numeric and is named DT and it is in a single observation dataset named HAVE.  You could use this step to make a macro variable named DTSTRING with the string you showed.

data _null_;
  set have;
  call symputx('dtstring',put(dt,dtdate9.)||' '||put(dt,tod8.));
run;

Example:

1    data _null_;
2      dt = '20MAR2025:00:00:00.000000'dt;
3      call symputx('dtstring',put(dt,dtdate9.)||' '||put(dt,tod8.));
4    run;

NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


5
6    %put |&dtstring|;
|20MAR2025 00:00:00|

[Ksharp]

proc format;
picture fmt
low-high='%0Y-%0m-%0d %0H:%0M:%0s'(datatype=datetime);
run;


data have;
    datetime='20MAR2025:00:00:00.000000'dt;
	format datetime fmt.;
    call symputx('datetime',put(datetime,fmt.));
run;
%put &=datetime.;