Desktop productivity for business analysts and programmers

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

Accepted Solution Solved
Reply
Contributor
Posts: 21
Accepted Solution

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

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 


Accepted Solutions
Solution
‎10-04-2016 09:03 AM
Respected Advisor
Posts: 3,836

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

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

View solution in original post


All Replies
Grand Advisor
Posts: 10,211

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

Did you have an "END AS VaraibleName" ?

need to have a place to put the result

Contributor
Posts: 21

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

Yes I do
Esteemed Advisor
Posts: 6,669

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

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 21

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

Thank you Kurt, the problem was the date format

Solution
‎10-04-2016 09:03 AM
Respected Advisor
Posts: 3,836

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

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 356 views
  • 2 likes
  • 4 in conversation