Desktop productivity for business analysts and programmers

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

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 129
Accepted Solution

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

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


Accepted Solutions
Solution
‎09-26-2017 07:59 PM
Super User
Posts: 23,980

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


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;

View solution in original post


All Replies
Valued Guide
Posts: 596

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

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?

PROC Star
Posts: 392

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

Not really shure what you want, but this might help Smiley Happy

 

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;

Solution
‎09-26-2017 07:59 PM
Super User
Posts: 23,980

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


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;
Frequent Contributor
Posts: 129

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

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

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

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.
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 258 views
  • 0 likes
  • 4 in conversation