Looking to find difference between Admin and Discharge dates

[PilOSU]

I have data for admin and discharge dates. They are both numeric values, with formats of DATETIME and the informat ANYDTDTM40. 

I want to characterize the months of each observation into four categories, (March-May, June-August, September-November, December-February) and put it into a proc tabulate table that i have already made. Can anyone point me in the right direction of what code to look at? 

[JOL]

One method is to create a custom format using Proc Format and apply the custom format in your Proc Tabulate code;  See sample code below:

proc format;
value dtm "01MAR2021:00:00:00"DT - "31MAY2021:00:00:00"DT = "March-May"
"01JUN2021:00:00:00"DT - "31AUG2021:00:00:00"DT = "June-August"
"01SEP2021:00:00:00"DT - "30NOV2021:00:00:00"DT = "September-November"
"01DEC2021:00:00:00"DT - "28FEB2022:00:00:00"DT = "December-February";
run;

data test;
admin= "25MAR2021:00:00:00"DT;
discharge = "18nov2021:00:00:00"DT;
format admin discharge dtm.;
run;

proc tabulate data=test format=dtm.;
var admin discharge;
table admin discharge;
run;

[PilOSU]

My dates span many different years from 2006 to 2021, is there a way to change the code to express this, because it with the code supplied it just returns the values in my dataset. 

[JOL · Accepted solution]

Automated the code with macro variables to produce custom format for the range of dates you specified.

%macro dtmm;
proc format;
value dtm
%do i=2006 %to 2021;
"01MAR&i.:00:00:00"DT - "31MAY&i.:00:00:00"DT = "March-May"
"01JUN&i.:00:00:00"DT - "31AUG&i.:00:00:00"DT = "June-August"
"01SEP&i.:00:00:00"DT - "30NOV&i.:00:00:00"DT = "September-November"
"01DEC&i.:00:00:00"DT - "28FEB%eval(&i+1):00:00:00"DT = "December-February"
%end;
;
run;
%mend dtmm;
%dtmm

[PilOSU]

so that is what macros can do... Thank you!

[PilOSU]

I have one date that's on the leap day in 2016. Do you know how would i account for that? @JOL 

[FreelanceReinh]

Hello @PilOSU,

---

@PilOSU wrote:

I have one date that's on the leap day in 2016. Do you know how would i account for that? @JOL 

---

You could simplify the format to operate on plain month numbers (1, 2, ..., 12) and then apply it to month(datepart(your_datetime_value)).

Or use existing SAS functions instead of a user-defined format to get from the month number to the appropriate range of months:

data test;
have='29FEB2016:12:34:56'dt; /* arbitrary datetime value */
length want $7;
want=choosec(mod((month(datepart(have))+9)/3,4)+1,
             'Mar-May','Jun-Aug','Sep-Nov','Dec-Feb');
run;

[JOL]

2016 was not a leap year so you may have a typo in the data.  I modified the macro to account for leap years between 2004 to 2024.

%macro dtmm;
proc format;
value dtm
%do i=2006 %to 2021;
"01MAR&i.:00:00:00"DT - "31MAY&i.:00:00:00"DT = "March-May"
"01JUN&i.:00:00:00"DT - "31AUG&i.:00:00:00"DT = "June-August"
"01SEP&i.:00:00:00"DT - "30NOV&i.:00:00:00"DT = "September-November"
"01DEC&i.:00:00:00"DT - "28FEB%eval(&i+1):00:00:00"DT = "December-February"

%end;
%do i=2004 %to 2024 %by 4;
"29FEB&i.:00:00:00"DT = "December-February"
%end;
;
run;
%mend dtmm;
%dtmm