topic Convert daily Dates to Australian Financial Year quarters (July - Sept, Oct - Dec etc) in SAS Enterprise Guide

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

DME790 — Thu, 21 Sep 2017 06:05:43 GMT

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

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

ChrisBrooks — Thu, 21 Sep 2017 07:42:12 GMT

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?

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

FredrikE — Thu, 21 Sep 2017 10:46:06 GMT

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;

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

Reeza — Thu, 21 Sep 2017 15:23:49 GMT

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

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

DME790 — Wed, 27 Sep 2017 00:00:34 GMT

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

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

Reeza — Wed, 27 Sep 2017 03:26:03 GMT

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.