turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- General Programming
- /
- FUNKY RESULT - MACRO VARIABLES

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-09-2014 02:58 PM

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;

Accepted Solutions

Solution

09-09-2014
04:06 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-09-2014 04:06 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-09-2014 03:20 PM

what do those values look like in a put statement?

i.e.

%put &valuationDate;

%put &maturityDate;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-09-2014 03:53 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-09-2014 03:58 PM

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;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-09-2014 03:20 PM

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?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-09-2014 04:01 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-09-2014 04:06 PM

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