FUNKY RESULT - MACRO VARIABLES

[maroulator]

I have the following code, which I apply to the attached.csv file. My end goal is to calculate the number of days between the macro variables &MaturityDate and &ValuationDate. Unfortunately, I am not getting a reasonable result (see arrow below). Could someone please provide some insight?

Thanks in advance.

data WORK.NCDebt;
     infile '/fmacdata/utility/fin/KBenchmarks/SAS Data/NCDebt_1.csv' dsd lrecl=40000 firstobs=2;
informat ValuationDate yymmdd.;
informat MaturityDate mmddyy10.;
format MaturityDate yymmdd10. ValuationDate yymmdd10.;
input ValuationDate $ MaturityDate $;
run;

proc transpose data=Ncdebt
  out=Ncdebt2;
  var ValuationDate MaturityDate;
run;

data Ncdebt3;
  set Ncdebt2(rename=(_NAME_=Var1));
  keep Var1 COL1;
run;

proc sql;
  select COL1
  into: ValuationDate
  from Ncdebt3
  where var1='ValuationDate';
quit;

proc sql;
  select COL1
  into: MaturityDate
  from Ncdebt3
  where var1='MaturityDate';
quit;

%let Result=%eval(&ValuationDate-&MaturityDate);   ------>THIS PART IS NOT GIVING ME THE CORRECT RESULTS

%put &ValuationDate &MaturityDate &Result;

[Reeza]

what do those values look like in a put statement?

i.e.

%put &valuationDate;

%put &maturityDate;

[maroulator]

ValuationDate resolves to 2014-03-31

MaturityDate resolves to 2041-05-15

Result resolves to -81, whereas I would have expected Result to resolve to 9907.

[Reeza]

This is what you're passing to SAS then

2014-03-31-2041-05-15 which does actually equal -81.

SAS isn't recognizing it those values as dates, you'll need to change them to be actual sas dates somehow, either when you create them or in the calculation step. I'd recommend the creation step.

proc sql;

  select input(COL1, anydtdte.)

  into: MaturityDate

  from Ncdebt3

  where var1='MaturityDate';

quit;

[PaigeMiller]

Unfortunately, I am not getting a reasonable result (see arrow below). Could someone please provide some insight?

%let Result=%eval(&ValuationDate-&MaturityDate);   ------>THIS PART IS NOT GIVING ME THE CORRECT RESULTS

What results are you getting? Why are they not reasonable? What did you expect to get?

[Hima]

If you can adjust your date format, it can be acheived easily with the below code. Hope it will help you a bit

%macro diff_date
   (begin =
   ,end   = today()
   ,y     = years
   ,m     = months
   ,d     = days
    );
*--number of full months between dates;
&m = intck('month', &begin, &end)
  -(day(&end) < day(&begin));

*--number of full years between dates;
&y = floor(&m / 12);

*--remainder of full months after removing full years;
&m = mod(&m , 12);

*--number of days remaining after removing years and months;
&d = ( day(&end) < day(&begin))
  * day(intnx('month',&begin,1)-1)
  + day(&end)
  - day(&begin);
%mend;

data _null_;
ValuationDate= '31jan2008'd;
MaturityDate= '01mar2009'd;

%diff_date(begin=ValuationDate, end=MaturityDate)

put years= months= days= ;
run;

[Tom · Accepted solution]

You are probably creating macro variables with the dates formatted with the hyphens in them.

%let valuationDate= 2014-03-31;

%let maturityDate=2041-05-15 ;

So when you tried to use %EVAL() to perform arithmetic on them it happily subtracted the month number from the year number and then subtracted the day number from that.

A value of minus 81 looks right to me.

You can either tell PROC SQL to format the dates as an integer to get it as a simple number of days and then your subtraction will work.

select COL1 format=10.

Our you could tell it to use DATE9 format and then reference them as date literals.  You might need to use %sysevalf() instead of %eval() for that to work.

select COL1 format=date9.

...

%let Result=%sysevalf( "&ValuationDate"d - "&MaturityDate"d );