topic Re: Incrementing Date Outside of Data Step in New SAS User

Incrementing Date Outside of Data Step

DavidPhillips2 — Thu, 13 Jun 2019 19:51:13 GMT

How do I change a date variable outside of a date step?

I'm trying syntax like.

%let todaydate1=%eval(intnx('year',&todaydate1,-1));

The idea is to call this line several times.

Re: Incrementing Date Outside of Data Step

Reeza — Thu, 13 Jun 2019 20:04:47 GMT

Functions get wratpped in %SYSFUNC() so if you're using INTNX() you need to wrap it in %SYSFUNC().

If you're doing the addition method, +1 with no INTNX function then you can use %EVAL instead.

Re: Incrementing Date Outside of Data Step

Tom — Thu, 13 Jun 2019 20:05:23 GMT

@DavidPhillips2 wrote:

How do I change a date variable outside of a date step?

I'm trying syntax like.

%let todaydate1=%eval(intnx('year',&todaydate1,-1));

The idea is to call this line several times.

You cannot have a variable outside of a dataset. Looks like you are trying to change the value of macro variable.

The %EVAL() function only works on strings that look like integer arithmetic and the arithmetic and the resulting integer is then converted back into a string. It will not understand the quotes or the letter INTNX.

You can use the %SYSFUNC() macro function to access the INTNX() function. Do not add quotes around YEAR as INTNX() does not recognize the interval 'YEAR'.

So assuming that TODAYDATE1 has either a date literal, like '13JUN2019'd, or a number of days value, like 21713 then this code will work.

%let todaydate1=%sysfunc(intnx(year,&todaydate1,-1));

So if TODAYDATE1 was either of my examples then it will become 21185. Which is the number that represents the first day in 2018.

Re: Incrementing Date Outside of Data Step

ballardw — Thu, 13 Jun 2019 20:06:02 GMT

@DavidPhillips2 wrote:

How do I change a date variable outside of a date step?

I'm trying syntax like.

%let todaydate1=%eval(intnx('year',&todaydate1,-1));

The idea is to call this line several times.

Calling data step functions requires use of %sysfunc () , not %eval.

%let todaydate1=%sysfunc(intnx('year',&todaydate1,-1));

might work depending on the actual content of &todaydate1.

If you have multiple function calls each one needs its own %sysfunc () which can lead to pretty ugly code.

Re: Incrementing Date Outside of Data Step

DavidPhillips2 — Thu, 13 Jun 2019 20:43:49 GMT

data todaytable;
tdate = today();
run;

proc sql noprint;
select "'"||put(tdate,date9.)||":00:00:00'dt" into :todaydate1
from todaytable;

quit;

When I try to increment it. The variable appears to go blank.

Re: Incrementing Date Outside of Data Step

Reeza — Thu, 13 Jun 2019 20:46:32 GMT

Try this and it's helpful to post your full code - including the increment section.

proc sql noprint;
select dhms(today(), 0, 0, 0) into :todaydate1;
quit;

Re: Incrementing Date Outside of Data Step

DavidPhillips2 — Thu, 13 Jun 2019 20:51:30 GMT

data todaytable;
tdate = today();
run;

proc sql noprint;
select "'"||put(tdate,date9.)||":00:00:00'dt" into :todaydate1
from todaytable;
select "'"||put(tdate,date9.)||":23:59:59'dt" into :todaydate2
from todaytable;
quit;

%Global differenceText AcademicPeriodCounter;
%let differenceText = %Str();

%macro createRegBioArchTables(RegBioCounter);

proc sql;
create table studentsUD&RegBioCounter. as
SELECT ACADEMIC_PERIOD,
FROM mytable
where
rundate >= &todaydate1. and rundate <= &todaydate2.
quit;

%mend;

%createRegBioArchTables(1);
%let todaydate1=%sysfunc(intnx(year,&todaydate1,-1));
%let todaydate2=%sysfunc(intnx(year,&todaydate2,-1));
%createRegBioArchTables(2);

proc sql;
select count(*)
FROM mytable
where t1.rundate >= &todaydate1. and t1.rundate <= &todaydate2.;
quit;

%put( test &todaydate1.);

Re: Incrementing Date Outside of Data Step

Tom — Thu, 13 Jun 2019 20:51:35 GMT

@DavidPhillips2 wrote:

data todaytable;
tdate = today();
run;

proc sql noprint;
select "'"||put(tdate,date9.)||":00:00:00'dt" into :todaydate1
from todaytable;

quit;

When I try to increment it. The variable appears to go blank.

Why are you getting a today's date and converting it into a datetime literal in a macro variable?

data _null_;
  call symputx('todaydatetime1',cats('"',put(today(),date9.),':00:00:00"dt');
run;

What you are planning to do with this macro variable?

Why not just leave it as the number of seconds since 1960?

data _null_;
  call symputx('todaydatetime1',intnx('dtday',datetime(),0));
run;

Re: Incrementing Date Outside of Data Step

DavidPhillips2 — Thu, 13 Jun 2019 20:54:20 GMT

The purpose is to query a set of records within a day and do the same query repetitively one year back at a time.

Re: Incrementing Date Outside of Data Step

Reeza — Thu, 13 Jun 2019 20:56:18 GMT

Are you trying to get daily reporting this way? There are much easier ways to do that? Or generic period reporting?

Re: Incrementing Date Outside of Data Step

Reeza — Thu, 13 Jun 2019 20:58:12 GMT

Why not just group it by changing the datetime variable to a date? And using either GROUP or a BY statement?

Re: Incrementing Date Outside of Data Step

Tom — Thu, 13 Jun 2019 21:03:59 GMT

@DavidPhillips2 wrote:

The purpose is to query a set of records within a day and do the same query repetitively one year back at a time.

That is not what the code you posted is doing. It is getting the data for a single day. Then you are trying to change to the first day of the year before and are getting the data for that January 1st day. To go back exactly a year you need to use SAME as the last parameter to the INTNX() function call.

Do you want the data grouped by day? Why not just create a DATE value from your DATETIME values?

Do you want the data grouped by year?

Do you want a rolling interval of 365 (366) days?

Re: Incrementing Date Outside of Data Step

DavidPhillips2 — Thu, 13 Jun 2019 21:04:33 GMT

Its a point in time comparison dataset. State of data at a particular date compared to a year back. So querying the date once per year and unioning it with multiple years.

Re: Incrementing Date Outside of Data Step

Tom — Thu, 13 Jun 2019 21:07:42 GMT

@DavidPhillips2 wrote:

Its a point in time comparison dataset. State of data at a particular date compared to a year back. So querying the date once per year and unioning it with multiple years.

So make one variable with the day of the year and one variable with the year.

data want ;
  set have ;
  year=year(datepart(rundate));
  day_of_year =datepart(rundate) - intnx('year',datepart(rundate),0) + 1;
run;

Re: Incrementing Date Outside of Data Step

Tom — Thu, 13 Jun 2019 21:23:05 GMT

Since you don't care about the time of day don't include it in your macro variable. You don't need two macro variables.

%let date=%sysfunc(today(),date9);

Then use that value in your code

rundate between "&date:00:00:00"dt and "&date:23:59:59"dt

Then to change it use the INTNX() function with YEAR interval (instead of the DTYEAR interval your would have needed for a datetime value).

%let date=%sysfunc(intnx(year,"&date"d,-1,s),date9);

Re: Incrementing Date Outside of Data Step

Reeza — Thu, 13 Jun 2019 21:29:00 GMT

Assuming you have some reason for doing this, or are too far down this route to consider a redesign here's a modification that may help:

Some changes:
1. Generate a temp table instead of multiple and append them to the master at the top of the program

2. Call the macro from a data step directly, incrementing the dates there to avoid any macro issues.

3. Add a datepart() to simply the filtering

4. clean up after

If this doesn't apply, feel free to ignore it!

Note that dates don't have to be formatted as dates for this to work, the number values are perfectly fine.

%Global differenceText AcademicPeriodCounter;
%let differenceText = %Str();



%macro createRegBioArchTables(RegBioCounter=, ddate = );

 

proc sql;
create table _temp as
SELECT ACADEMIC_PERIOD, 
FROM mytable
where 
datepart(rundate) = &ddate.;
quit;

proc append base = want data =_temp force;
run;

proc sql;
drop table _temp;
quit;

%mend;

data demo;
do date='01Jan2019'd to '31Jan2019'd;
str = catt('%createRegBioArchTables(RegBioCounter=', _n_,  ' ,ddate=', date, ');');
output;
*call execute(str);
end;

run;

Re: Incrementing Date Outside of Data Step

DavidPhillips2 — Fri, 14 Jun 2019 15:02:59 GMT

I'm querying today's date to see what datetimes stored in the large transaction table fall within today's date. The feeding the two date ranges into a where clause.

If I use the number of seconds from 1960, how do I insert the result into a where clause and increment it back a year.

Re: Incrementing Date Outside of Data Step

DavidPhillips2 — Fri, 14 Jun 2019 15:13:05 GMT

I think this branch is the right way to go working on some minor details with it.

Re: Incrementing Date Outside of Data Step

ballardw — Fri, 14 Jun 2019 15:16:52 GMT

@DavidPhillips2 wrote:

I'm querying today's date to see what datetimes stored in the large transaction table fall within today's date. The feeding the two date ranges into a where clause.

If I use the number of seconds from 1960, how do I insert the result into a where clause and increment it back a year.

Same as changing a date by a year, use the INTNX function. Use interval DTYEAR so SAS know you are incrementing a datetime value.

data example;
   x= dhms(today(),9,15,20);
   format x datetime20.;
   y1 = intnx('dtyear',x,-1,'b');
   y2 = intnx('dtyear',x,-1,'s');
   y3 = intnx('dtyear',x,-1,'e');
   format y: datetime20.;
run;