BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
maroulator
Obsidian | Level 7

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

View solution in original post

6 REPLIES 6
Reeza
Super User

what do those values look like in a put statement?

i.e.

%put &valuationDate;

%put &maturityDate;

maroulator
Obsidian | Level 7

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
Super User

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
Diamond | Level 26

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
Hima
Obsidian | Level 7

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;

Capture.JPG

Tom
Super User Tom
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 804 views
  • 6 likes
  • 5 in conversation