Convert daily Dates to Australian Financial Year quarters (July - Sept, Oct - Dec etc)

[DME790]

Hi All,

I need to create (or change) the 'event_dt' variable to match the relevant Australian Financial year quarters by several years;

qtr 1 = Jul - Sep

Qtr 2 = Oct - Dec

Qtr 3 = Jan - Mar

Qtr 4 = Apr - Jun

I have tried several methods however the results still returns a date value which then doesn't allow me to Summarize by the quarter/year.

Hope this makes sense.

Regrads

Dean

[ChrisBrooks]

Can you show us what your data looks like by creating a simple data step to import it into SAS along with your desired output?

[FredrikE]

Not really shure what you want, but this might help 🙂

data test;

event_dt = '21feb2017:13:52:50'dt;

year = year(datepart(event_dt));

qtr = qtr(datepart(event_dt));

select (qtr);

when (1) qtrA = 3;

when (2) qtrA = 4;

when (3) qtrA = 1;

when (4) qtrA = 2;

otherwise;

end;

run;

[Reeza · Accepted solution]

---

@DME790 wrote:

I have tried several methods however the results still returns a date value which then doesn't allow me to Summarize by the quarter/year.

---

Please include what you've tried in the future. 

You don't need to make a conversion to summarize by quarter/year, you need to apply the correct format and SAS summarizes using the formatted value. See the PROC FREQ example below which uses the SAS date to do the counts.

*Create a sample data set;
data have;
    do i=1 to 12;
        sas_date = mdy(i, 15, 2017);
        output;
    end;
run;

*create the quarter variable as a SAS date or a number;
data want;
    set have;

    *since your quarters are 6 months off the standard def, move date back 6 months;
    quarter_date = intnx('month', sas_date, -6, 's');
    quarter_number=qtr(quarter_date);

    format sas_date date9. quarter_date qtr4.;

run;

*count # / quarter using SAS date;
proc freq data=want order=freq;
table quarter_date;
run;

[DME790]

Thanks Reeza - can you let me know what the 'S' does in the quarter_date = intnx('month', sas_date, -6, 's');
Cheers
Dean

[Reeza]

The fourth parameter is alignment option. In this case I've used 15 as the date and set it to the same since all months have a 15. Other options are beginning, end, middle. See the docs for more details.