issues with Macros and Date formats

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 134
Accepted Solution

issues with Macros and Date formats

Hi,

I am a new Bee with SAS and a bit struggling with Macros and Date formats... In my macro I've defined date format as DATETIME20. to match with my WORK.TABLE_B format

Here is the macro and the code that I've used


/**********MACRO**********/

options byline;


%let today = %sysfunc(today());

%let NEXT_DAY_1 = %sysfunc(putn(%sysfunc(intnx(day,&today,1)),DATETIME20.));

%let NEXT_DAY_2 = %sysfunc(putn(%sysfunc(intnx(day,&today,2)),DATETIME20.));

/*************************CODE*********************/

/*******START_DATE is a date with format: DATETIME20.*************/

PROC SQL;

CREATE TABLE WORK.TABLE_A AS

SELECT t1.*

FROM WORK.TABLE_B t1

WHERE t1.START_DATE BETWEEN

"&NEXT_DAY_1"d AND "&NEXT_DAY_2"d;

RUN;



please advice

Thanks in advance


Accepted Solutions
Solution
‎06-23-2012 09:42 PM
Super Contributor
Posts: 1,636

Re: issues with Macros and Date formats

Hi,

try

%let today = %sysfunc(today());

%let NEXT_DAY_1 = %sysfunc(dhms(%sysfunc(intnx(day,&today,1)),7,0,0),DATETIME22.);

%let NEXT_DAY_2 = %sysfunc(dhms(%sysfunc(intnx(day,&today,2)),7,0,0),DATETIME22.);

%put &NEXT_DAY_1;

%put &NEXT_DAY_2;

log file:

105  %let today = %sysfunc(today());

106  %let NEXT_DAY_1 =

106! %sysfunc(dhms(%sysfunc(intnx(day,&today,1)),7,0,0),DATETIME22.);

107  %let NEXT_DAY_2 =

107! %sysfunc(dhms(%sysfunc(intnx(day,&today,2)),7,0,0),DATETIME22.);

108

109  %put &NEXT_DAY_1;

24JUN2012:07:00:00

110  %put &NEXT_DAY_2;

25JUN2012:07:00:00

View solution in original post


All Replies
Super Contributor
Posts: 349

Re: issues with Macros and Date formats

Hi,

Try this...hope it helps..

%let today = %sysfunc(today());

%let NEXT_DAY_1 = %sysfunc(dhms(%sysfunc(intnx(day,&today,1)),0,0,0));

%let NEXT_DAY_2 = %sysfunc(dhms(%sysfunc(intnx(day,&today,2)),0,0,0));

PROC SQL;

CREATE TABLE WORK.TABLE_A AS

SELECT t1.*

FROM WORK.TABLE_B t1

WHERE t1.START_DATE BETWEEN

&NEXT_DAY_1 AND &NEXT_DAY_2;

RUN;

Thanks,

Shiva

Frequent Contributor
Posts: 134

Re: issues with Macros and Date formats

Hi Shiva,

I've tried your macro and got the following output

%put &NEXT_DAY_1 &NEXT_DAY_2;

1656115200 1656201600

Thanks

Super Contributor
Posts: 1,636

Re: issues with Macros and Date formats

HI,

1    %let today = %sysfunc(datetime());

2    %let NEXT_DAY_1 = %sysfunc(intnx(dtday,&today,1,b),DATETIME22.);

3    %let NEXT_DAY_2 = %sysfunc(intnx(dtday,&today,2,b),DATETIME22.);

4

5    %put _user_;

GLOBAL NEXT_DAY_1 24JUN2012:00:00:00

GLOBAL NEXT_DAY_2 25JUN2012:00:00:00

GLOBAL TODAY 1656080913.39

Linlin

Respected Advisor
Posts: 4,173

Re: issues with Macros and Date formats

A SAS date value is the number of days since 1/1/1960, a SAS datetime value is the number of seconds since 1/1/1960.

If the comment in your code "START_DATE is a date with format: DATETIME20." would be correct then all dates (number of days) with a datetime20. format applied would print as 1/1/1960.

Assuming "START_DATE" is actually a datetime value, you must also generate datetime values for all other variables. Your code could then look like:

%let today = %sysfunc(datetime());
%let NEXT_DAY_1 = %sysfunc(intnx(dtday,&today,1,b),DATETIME20.);
%let NEXT_DAY_2 = %sysfunc(intnx(dtday,&today,2,b),DATETIME20.);

PROC SQL;
CREATE TABLE WORK.TABLE_A AS
SELECT t1.*
FROM WORK.TABLE_B t1
WHERE t1.START_DATE BETWEEN
"&NEXT_DAY_1"dt AND "&NEXT_DAY_2"dt;
RUN;

As a specialty of %sysfunc(): You can use a format directly on the result of a function. You don't need a separate "putn()".

As datetime values are numeric values you could also use the numbers instead of the formated strings like:

%let today = %sysfunc(datetime());

PROC SQL;
CREATE TABLE WORK.TABLE_A AS
SELECT t1.*
FROM WORK.TABLE_B t1
WHERE t1.START_DATE BETWEEN
%sysfunc(intnx(dtday,&today,1,b) AND %sysfunc(intnx(dtday,&today,1,b);
RUN;

Frequent Contributor
Posts: 134

Re: issues with Macros and Date formats

Hi Patrick,

I've tried tried your macro and got the following output

/* here is the macro */

options byline;

%let today = %sysfunc(today());

%let NEXT_DAY_1 = %sysfunc(intnx(dtday,&today,1),DATETIME20.);

%let NEXT_DAY_2 = %sysfunc(intnx(dtday,&today,2),DATETIME20.);

/*here is the output format */

%put &NEXT_DAY_1 &NEXT_DAY_2;

02JAN1960:00:00:00 03JAN1960:00:00:00

I was expecting my Next_day_1 to be 24June2012 00:00:00 and Next_day_2 to be 25Jun2012 00:00:00

thanks again for your assistance

Super User
Super User
Posts: 7,078

Re: issues with Macros and Date formats

You set TODAY to the number of days since 1960 and then used it as a the number of seconds since 1960.

Change

%let today=%sysfunc(today());

to

%let today=%sysfunc(datetime());

Frequent Contributor
Posts: 134

Re: issues with Macros and Date formats

perfect. it worked.

The only thing I may ask to please let me know how to change the date to 7 AM.... as an example, right now I am getting date as 23JUN2012:00:00:00 but I want it to show as 23JUN2012:00:07:00

Thanks again for your assistance

Super User
Super User
Posts: 7,078

Re: issues with Macros and Date formats

You could look at the example above from shivas using the DHMS (days hours minutes seconds) function.

Or just add 7 hours to the value when you use it in your query.  Or equivalent subtract the 7 hours from the dataset variable.

WHERE t1.START_DATE - '07:00't  BETWEEN

"&NEXT_DAY_1"dt AND "&NEXT_DAY_2"dt;

Respected Advisor
Posts: 3,156

Re: issues with Macros and Date formats

You are dealing with datetime with 'second' information, so you probably need to use 'second' as shifting unit.

%let today = %sysfunc(datetime());

%let NEXT_DAY_1 = %sysfunc(intnx(dtsecond,&today,86400),DATETIME20.);

%put &NEXT_DAY_1;

Haikuo

Update: you can actually use the alignment operator 's' to get the same result without using 'second':

%let NEXT_DAY_1 = %sysfunc(intnx(dtday,&today,1,s),DATETIME20.);

Frequent Contributor
Posts: 134

Re: issues with Macros and Date formats

got a bit confused here... so to keep it simple, here is the code that I've used that was suggested by Linlin

%let today = %sysfunc(datetime());

%let NEXT_DAY_1 = %sysfunc(intnx(dtday,&today,1,b),DATETIME22.);

%let NEXT_DAY_2 = %sysfunc(intnx(dtday,&today,2,b),DATETIME22.);

can you please modify it to let me know how to capture 7 AM

Thanks again everyone... this is my first experience within this community and you folks are the best so far

Solution
‎06-23-2012 09:42 PM
Super Contributor
Posts: 1,636

Re: issues with Macros and Date formats

Hi,

try

%let today = %sysfunc(today());

%let NEXT_DAY_1 = %sysfunc(dhms(%sysfunc(intnx(day,&today,1)),7,0,0),DATETIME22.);

%let NEXT_DAY_2 = %sysfunc(dhms(%sysfunc(intnx(day,&today,2)),7,0,0),DATETIME22.);

%put &NEXT_DAY_1;

%put &NEXT_DAY_2;

log file:

105  %let today = %sysfunc(today());

106  %let NEXT_DAY_1 =

106! %sysfunc(dhms(%sysfunc(intnx(day,&today,1)),7,0,0),DATETIME22.);

107  %let NEXT_DAY_2 =

107! %sysfunc(dhms(%sysfunc(intnx(day,&today,2)),7,0,0),DATETIME22.);

108

109  %put &NEXT_DAY_1;

24JUN2012:07:00:00

110  %put &NEXT_DAY_2;

25JUN2012:07:00:00

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 11 replies
  • 787 views
  • 1 like
  • 6 in conversation