turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- General Programming
- /
- issues with Macros and Date formats

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-22-2012 05:38 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-23-2012 09:42 PM

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

All Replies

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-23-2012 02:45 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-23-2012 02:17 PM

Hi Shiva,

I've tried your macro and got the following output

%put &NEXT_DAY_1 &NEXT_DAY_2;

1656115200 1656201600

Thanks

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-23-2012 02:32 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-23-2012 04:28 AM

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;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-23-2012 02:15 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-23-2012 02:37 PM

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());

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-23-2012 05:10 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-23-2012 06:07 PM

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;**

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-23-2012 06:19 PM

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.);

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-23-2012 09:15 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-23-2012 09:42 PM

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