Incrementing Date Outside of Data Step

[DavidPhillips2]

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.

[Reeza]

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.

[Tom · Accepted solution]

---

@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.

[ballardw]

---

@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.

[DavidPhillips2]

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.

[Reeza]

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;

[DavidPhillips2]

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

[Reeza]

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

[Reeza]

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

[DavidPhillips2]

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.

[Tom]

---

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

[Tom]

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

[DavidPhillips2]

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

[Reeza]

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;

[Tom]

---

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