Filter a table differently depending on conditions

gorba004 — Thu, 30 Jan 2020 04:48:39 GMT

Hello! I am currently working on a problem where I need to filter a data set differently depending on conditions about that table. The program I am writing will be run on a weekly basis and there may be some times it needs to filter in different ways. The table has a date column and a status column. If the max year in the data set is equal to the current year there is a specific way it needs to be filtered, and if it is not equal to the current year there will be a change to the filter.

I have tried using if logic with multiple set statements in a data step, and found that is not possible (maybe?). I have also tried proc sql with case when statements and have had no success. Lastly, I have tried using a macro variable, but am inexperienced with working with macros. Here is an example of what I have tried:

DATA want; 
SET have; 
IF MAX(YEAR(have.DATE)) = YEAR(TODAY()) THEN 
SET have(WHERE=(have.DATE >= INTNX('Year', have.DATE, 0, 'B') AND have.STATUS = 'Approved')); 
ElSE SET have(WHERE=(have.DATE >= INTNX('Year', have.DATE, -1, 'B') AND have.STATUS = 'Approved'));
RUN;

Essentially, if the max date is in the current year then I need the rows in that year, and if the max date is in the previous year, than I need all of the rows from that year.

Re: Filter a table differently depending on conditions

ErikLund_Jensen — Thu, 30 Jan 2020 17:43:46 GMT

Hi @gorba004

You can't do it all in the same data step, so here is how you could use proc sql to determine if current year is present in your input and then use macro code to execute an appropriate data step depending on the result. Try to experiment with dates in the test input.

* Test data;
data have; 
	format date date9.;
	DATE = '01feb2018'd; STATUS = 'Approved'; output;
	DATE = '01feb2019'd; STATUS = 'Approved'; output;
	DATE = '01feb2020'd; STATUS = 'Approved'; output;
run;

* Create boolean variable 0/1 for current year present in have;
proc sql noprint;
	select max(year(DATE)) = year(date()) into :currentyear
	from have;
quit;
%put &=currentyear;

* Macro conditional execution of one of the two data steps depending on value of currentyear;
%if &currentyear = 1 %then %do;
	data want; 
		set have;
		if DATE >= intnx('Year', date(), 0) AND STATUS = 'Approved'; 
	run;
%end;
%else %do;
	DATA want; 
		set have;
		if DATE >= intnx('Year', date(), -1) AND STATUS = 'Approved'; 
	run;
%end;

topic Filter a table differently depending on conditions in SAS Procedures

Filter a table differently depending on conditions

Re: Filter a table differently depending on conditions