topic Re: Create a MTD flag on SAS EG for a Year over Year analysis in SAS Enterprise Guide

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

chrisjab — Mon, 03 Oct 2016 14:16:47 GMT

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,

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

ballardw — Mon, 03 Oct 2016 14:20:12 GMT

Did you have an "END AS VaraibleName" ?

need to have a place to put the result

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

chrisjab — Mon, 03 Oct 2016 14:22:16 GMT

Yes I do

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

Kurt_Bremser — Mon, 03 Oct 2016 14:29:52 GMT

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.

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

Patrick — Tue, 04 Oct 2016 02:02:05 GMT

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

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

chrisjab — Tue, 04 Oct 2016 13:03:03 GMT

Thank you Kurt, the problem was the date format