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.
@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.
@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.
@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.
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.
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.);
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.
@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;
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);
I think this branch is the right way to go working on some minor details with it.
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;
@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;
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.