macro counts from date variable

[TPayne]

I have a macro that is not calculating correctly. I want to take a variable, date0, and create the macro, datecount out of it. 

date0 is formatted as a numeric mmddyy10. in the dataset report 

proc sql noprint;
select date0 into :datecount
from report; quit;

when i %put &datecount I can tell that the macro is not working. I am unsure if it is due to the formatting (i also tried formatting date0 as date9. and that did not seem to work either) or if there's something else i'm doing incorrectly. Ultimately, I want to call the macro in a different macro:

%let date2MO=%sysfunc(intnx(day, "&datecount"d, 91,e));  where I calculate whatever date is 91 days after datecount.

any insight is helpful! 

[PaigeMiller]

---

@TPayne wrote:

I have a macro that is not calculating correctly. I want to take a variable, date0, and create the macro, datecount out of it. 

date0 is formatted as a numeric mmddyy10. in the dataset report 

proc sql noprint;
select date0 into :datecount
from report; quit;

when i %put &datecount I can tell that the macro is not working. I am unsure if it is due to the formatting (i also tried formatting date0 as date9. and that did not seem to work either) or if there's something else i'm doing incorrectly. Ultimately, I want to call the macro in a different macro:

%let date2MO=%sysfunc(intnx(day, "&datecount"d, 91,e));  where I calculate whatever date is 91 days after datecount.

any insight is helpful! 

---

You can do a lot of debugging yourself.

Use %put &=datecount; after your PROC SQL to actually SEE with your own eyes what &DATECOUNT contains.

We also need to know what &DATECOUNT contains in order for us to help further. Please let us know.

However, I suspect you don't need "&DATECOUNT"D, you need just &DATECOUNT in the INTNX function.

Also, why are you talking about "counts"?

[TPayne]

Hello! Thanks!

When I check the macro wit ha %put statement it  just calculates a .  (I would expect it to mirror date0- see examples below). 

  date0

06/10/2019

       .

       .

07/14/2019

Ultimately I want to count 91 days after my datecount macro to get a new date

[PaigeMiller]

---

@TPayne wrote:

Hello! Thanks!

When I check the macro wit ha %put statement it  just calculates a .  (I would expect it to mirror date0- see examples below). 

  date0

---

This tells you that you don't get the desired result from your PROC SQL, and so the problem is the actual data set used or the logic used. So please look at what the values of DATE0 are in data set REPORT.

By the way, how many records are in data set REPORT, and is DATE0 missing in the first observation?

[TPayne]

That was my suspicion as well. The dataset itself has 278 obs; many date0 variables are missing (including the first) as this is a living dataset- i hadn't considered that yet. I can try sorting the dataset so missing obs are not first, but other than that I am unsure of what is wrong in the proc sql itself. 

[PaigeMiller]

---

@TPayne wrote:

That was my suspicion as well. The dataset itself has 278 obs; many date0 variables are missing (including the first) as this is a living dataset- i hadn't considered that yet. I can try sorting the dataset so missing obs are not first, but other than that I am unsure of what is wrong in the proc sql itself. 

---

No, that's not something that will get you further along. Don't do that.

In fact, I don't see the logic here at all. If you have 278 observations, what do you want to do with each observation? Do you want to add 91 days to each? Or 91 days based on some minimum/maximum/other statistic of the dates? 

And why do we need macros at all here just to add 91 days?

Please explain the larger picture here, exactly what you are trying to do, as right now it seems to me like you have developed a complicated method of doing something with macro variables that could be replaced by a much less complicated method that has no macro variables at all.

[TPayne]

Of course. I do think a macro is my my solution but yes ultimately I just need to add 91 days to date0 (but date0 will be different for all 278 observations). I want to create a variable "status" that gives insight in to a due date (the due date is 91 days past date0). Hopefully below helps put my goal in to perspective:

/*setting macro for today*/

%let today = %sysfunc(date());

/*setting macro for date0 -- will be different for every obs/some will be missing*/

proc sql noprint;
select DATE0 into :datecount
from report; quit;

/*calculating 91 days past date0*/

%let date2MO=%sysfunc(intnx(day, "&datecount"d,91,'e'));

data status;

set report;
IF (&TODAY. > DATE0) AND (&TODAY. < DATE0 + 30.44) THEN STATUS_2MO= "COMING DUE";
IF (&TODAY. => DATE0 + 30) AND (&TODAY. <= DATE0 + 90) THEN STATUS_2MO= "DUE BY &date2mo.";

IF (&TODAY. > DATE0 +90) THEN STATUS_2MO= "OVERDUE";
END;
run;

Where when I look at my dataset, status_2mo will either say "coming due" "due by ___" or "overdue"

[TPayne]

I should add that this will be ran monthly for an entire year not just once 

[PaigeMiller · Accepted solution]

---

@TPayne wrote:

Of course. I do think a macro is my my solution but yes ultimately I just need to add 91 days to date0 (but date0 will be different for all 278 observations). I want to create a variable "status" that gives insight in to a due date (the due date is 91 days past date0). Hopefully below helps put my goal in to perspective:

/*setting macro for today*/

%let today = %sysfunc(date());

/*setting macro for date0 -- will be different for every obs/some will be missing*/

proc sql noprint;
select DATE0 into :datecount
from report; quit;

/*calculating 91 days past date0*/

%let date2MO=%sysfunc(intnx(day, "&datecount"d,91,'e'));

data status;

set report;
IF (&TODAY. > DATE0) AND (&TODAY. < DATE0 + 30.44) THEN STATUS_2MO= "COMING DUE";
IF (&TODAY. => DATE0 + 30) AND (&TODAY. <= DATE0 + 90) THEN STATUS_2MO= "DUE BY &date2mo.";

IF (&TODAY. > DATE0 +90) THEN STATUS_2MO= "OVERDUE";
END;
run;

Where when I look at my dataset, status_2mo will either say "coming due" "due by ___" or "overdue"

---

So, no macro variables are needed here.

Also, not sure why you would add 30.44 to a given date, wouldn't adding 30 work just as well?

data status;
    set report;
    length status $ 20;
    today=date();
    if today>date0 and today<date0+30 then status_2mo='COMING DUE';
    if today>=date0+30 and today<=date0+90 then status_2mo=
        cat('DUE BY ',put(date0+90,date7.));
    if today>date0+90 then status_2mo='OVERDUE';
run;

[TPayne]

thank you so much for your time and help- that worked! and I learned how to code something more efficiently (always striving for this) 

[Astounding]

The formats don't get copied to the macro variable, just the value.  While you could change that in the SQL step, you don't need to.  This part could be changed instead:

%let date2MO=%sysfunc(intnx(day, "&datecount"d, 91,e));

An easier way to get the correct value:

%let date2MO=%eval(&datecount + 91);

If you want DATE2MO to be in a different format, add that as an additional statement:

%let date2MO = %sysfunc(putn(&date2MO, date9));

There are ways to combine the two statements, but keeping them separate helps illustrate the proper use of dates.