topic Creating Variables for Filter with Condition in SAS Enterprise Guide

Creating Variables for Filter with Condition

Renan_Crepaldi — Wed, 09 Dec 2020 14:44:32 GMT

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!

Re: Creating Variables for Filter with Condition

Kurt_Bremser — Wed, 09 Dec 2020 14:55:31 GMT

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.

Re: Creating Variables for Filter with Condition

Renan_Crepaldi — Wed, 09 Dec 2020 14:58:42 GMT

Thank you very much, Kurt!

And I'll take the advice.

Regards, Renan