comparing sas dates with today macro

[TPayne]

I have a dataset where I am comparing multiple dates. Depending on the first date (date0), I want to create a different value for the variables status_2mo, status_6mo, status_12mo:

date2 should be between 0-90 days of date0; if date2 is missing then: 

  *if today is between 0-30 days of date0  then status= coming due

  *if today is between 30-90 days of date0  then status= due

  *if today is > 90 days of date0  then status = overdue

date6 should be between 150 and 240 days of date0; if date6 is missing then:

  *if today is between 150-180 of date0  then status = coming due

  *if today is between 180-240 of date0  then status = due

  *if date6 is > 240 of date0  then status = due overdue

date12 should be between 330 and 390 days of date0; id date12 is missing then:

  *if today is between 330-360 of date0  then status = coming due

  *if today is between 360-390 of date0  then status= due

  *if today is > 390 of date0   then status = overdue 

Below is a snippet of my dateset:

PIN    DATE0           DATE2        DATE6    DATE12     STATUS_2mo   STATUS_6mo  STATUS_12mo

1      20190615

2      20190711

3      20190410        

4      20190105      20190310

I would expect Status_2mo to be "coming due" for pins 1-2

I would expect status_2mo to be "overdue" for pin 3

I would expect status_6mo to be "due" for pin 4

here is a snippet of my code:

%let today = %sysfunc(date(),YYMMDDN8.);

/*2 MONTH CHECK-IN*/
If date2=. then do;
If (&today. > date0) and (&today. < date0 + 30) then status_2mo= "coming due";
if status_2mo ne "coming due" and (&today. > date0 + 30) and (&today. < date0 + 90) then status_2mo= "due";
if status_2mo ne "coming due" and status_2mo ne "due" and (&today. > date0 +90) then status_2mo= "overdue";
end;
run;

What i'm getting instead is that every value, regardless of if date2 is missing, is being marked as "overdue". Any insight helps! Thank you!

[PaigeMiller]

For any of this to work, you do not want formatted dates. You want the dates to be actual SAS date values, which are integers representing the number of days since Jan 1, 1960. By the way, this has nothing to do with macros at all.

So, use

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

and your DATE variables should not be in the human readable for of 20190615, but they should be unformatted actual SAS date values. You may have to perform a transformation if the DATE variables are text, or convert them to actual SAS date values if they are the integer 20190615. (are they character or numeric? if numeric, are they formatted to show 20190615, or are they not formatted and show 20910615?)

[TPayne]

They are numeric and formatted to show 20190615. I will start with converting them to SAS dates and move from there- thank you! 

[PaigeMiller · Accepted solution]

If they are numeric and formatted ... don't do anything to them. It's just the macro variable &today that has to change.

[TPayne]

thank you so much, that macro worked.