BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Renan_Crepaldi
Obsidian | Level 7

Hi, everyone!

 

I'm working on SAS Guide 8.1 Update 1 (8.1.1.4580).

 

I've identified a flaw on my code now that we're going to the next year. It's objective is to create two date variables that I use on a query as a filter.

 

data _null_;
	FORMAT dtini dtfim DATETIME20.;
	dtini_aux = INTNX('YEAR', TODAY(), -1,'B');
	dtfim_aux = INTNX('MONTH', TODAY(), -1,'E');
	dtini=DHMS(dtini_aux,0,0,0);
	dtfim=DHMS(dtfim_aux,23,59,59);
	CALL SYMPUT ('DTINICIAL',dtini);
	CALL SYMPUT ('DTFINAL',dtfim);
RUN;

%PUT &DTINICIAL.;
%PUT &DTFINAL.;

&DTINICIAL = initial date
&DTFINAL = final date

 

It's currently working as intended, filtering the data from the beginning of the last year until last month. But when we enter 2021, it'll filter only the data from 2020, since the result of dtini_aux will be Jan/2020. Considering that my result should bring only data from months that are "finished", the correct filter should be Jan/2019 until Dec/2020.

 

I've tried to create a conditional function inside the script above, but it doesn't work:

dtini_aux = CASE WHEN MONTH(TODAY()) = 1 THEN INTNX('YEAR', TODAY(), -2,'B')
	ELSE INTNX('YEAR', TODAY(), -1,'B')
END;

Does anyone know how to adapt this part of the code to solve this problem?

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

CASE/WHEN is SQL syntax, in a data step you use IF/THEN/ELSE:

data _null_;
FORMAT dtini dtfim DATETIME20.;
if month(today()) = 1
then dtini_aux = INTNX('YEAR', TODAY(), -2,'B');
else dtini_aux = INTNX('YEAR', TODAY(), -1,'B'); 
dtfim_aux = INTNX('MONTH', TODAY(), -1,'E');
dtini=DHMS(dtini_aux,0,0,0);
dtfim=DHMS(dtfim_aux,23,59,59);
CALL SYMPUT ('DTINICIAL',dtini);
CALL SYMPUT ('DTFINAL',dtfim);
RUN;

Big hint for the future: do not use tabs in code; set your Enhanced Editor to replace tabs with blanks.

View solution in original post

2 REPLIES 2
Kurt_Bremser
Super User

CASE/WHEN is SQL syntax, in a data step you use IF/THEN/ELSE:

data _null_;
FORMAT dtini dtfim DATETIME20.;
if month(today()) = 1
then dtini_aux = INTNX('YEAR', TODAY(), -2,'B');
else dtini_aux = INTNX('YEAR', TODAY(), -1,'B'); 
dtfim_aux = INTNX('MONTH', TODAY(), -1,'E');
dtini=DHMS(dtini_aux,0,0,0);
dtfim=DHMS(dtfim_aux,23,59,59);
CALL SYMPUT ('DTINICIAL',dtini);
CALL SYMPUT ('DTFINAL',dtfim);
RUN;

Big hint for the future: do not use tabs in code; set your Enhanced Editor to replace tabs with blanks.

Renan_Crepaldi
Obsidian | Level 7
Thank you very much, Kurt!

And I'll take the advice.

Regards, Renan

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

Creating Custom Steps in SAS Studio

Check out this tutorial series to learn how to build your own steps in SAS Studio.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 930 views
  • 1 like
  • 2 in conversation