topic Re: FUNKY RESULT - MACRO VARIABLES in SAS Programming

FUNKY RESULT - MACRO VARIABLES

maroulator — Tue, 09 Sep 2014 18:58:27 GMT

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;

Re: FUNKY RESULT - MACRO VARIABLES

Reeza — Tue, 09 Sep 2014 19:20:07 GMT

what do those values look like in a put statement?

i.e.

%put &valuationDate;

%put &maturityDate;

Re: FUNKY RESULT - MACRO VARIABLES

PaigeMiller — Tue, 09 Sep 2014 19:20:33 GMT

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?

Re: FUNKY RESULT - MACRO VARIABLES

maroulator — Tue, 09 Sep 2014 19:53:13 GMT

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.

Re: FUNKY RESULT - MACRO VARIABLES

Reeza — Tue, 09 Sep 2014 19:58:06 GMT

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;

Re: FUNKY RESULT - MACRO VARIABLES

Hima — Tue, 09 Sep 2014 20:01:41 GMT

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;

Re: FUNKY RESULT - MACRO VARIABLES

Tom — Tue, 09 Sep 2014 20:06:36 GMT

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