Create a MTD flag on SAS EG for a Year over Year analysis

[chrisjab]

I am trying to create a MTD flad on SAS EG to compare financial figures year over year; below is the formula I came up with, however I am not getting any answers. Not sure, what I'm doing wrong here:

CASE

WHEN MONTH(t1.TransactionDate) = MONTH (TODAY()) AND DAY(t1.TransactionDate) <= DAY(TODAY())

THEN 'MTD'

END

I have a list of TransactionsDates for 2015 (Jan to Dec) and 2016 YTD.

Thanks,

CJ 

[ballardw]

Did you have an "END AS VaraibleName" ?

need to have a place to put the result

[chrisjab]

Yes I do

[Kurt_Bremser]

data have;
input transactiondate :yymmdd10.;
format transactiondate yymmddd10.;
cards;
2015-04-04
2015-10-02
2015-10-10
2016-04-04
2016-10-02
2016-10-10
;
run;

proc sql;
create table want as
select transactiondate,
case
when month(t1.TransactionDate) = month(today()) and day(t1.TransactionDate) <= day(today())
then 'MTD'
end as mtd
from have t1;
quit;

proc print noobs;
run;

delivers this result:

transactiondate    mtd

  2015-04-04          
  2015-10-02       MTD
  2015-10-10          
  2016-04-04          
  2016-10-02       MTD
  2016-10-10          

So to me, your query works as desired; if that is not the case with your code, post the complete SQL code and the log.

[chrisjab]

Thank you Kurt, the problem was the date format

[Patrick · Accepted solution]

Is TransactionDate a SAS Date or DateTime value. If it's DateTime then your When case would always be False.