## FUNKY RESULT - MACRO VARIABLES

Solved
Frequent Contributor
Posts: 126

# FUNKY RESULT - MACRO VARIABLES

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?

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;

Accepted Solutions
Solution
‎09-09-2014 04:06 PM
Super User
Posts: 8,111

## Re: FUNKY RESULT - MACRO VARIABLES

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 );

All Replies
Super User
Posts: 23,724

## Re: FUNKY RESULT - MACRO VARIABLES

what do those values look like in a put statement?

i.e.

%put &valuationDate;

%put &maturityDate;

Frequent Contributor
Posts: 126

## Re: FUNKY RESULT - MACRO VARIABLES

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.

Super User
Posts: 23,724

## Re: FUNKY RESULT - MACRO VARIABLES

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;

Posts: 3,018

## Re: FUNKY RESULT - MACRO VARIABLES

```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?

--
Paige Miller
Regular Contributor
Posts: 233

## Re: FUNKY RESULT - MACRO VARIABLES

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;

Solution
‎09-09-2014 04:06 PM
Super User
Posts: 8,111

## Re: FUNKY RESULT - MACRO VARIABLES

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 );

🔒 This topic is solved and locked.