topic Re: Change into Quarter in SAS Programming

Change into Quarter

Ahsan — Fri, 09 Mar 2018 05:05:49 GMT

Hello,

I just need some help I need to change the date into quarters. How do we do this please help.

SVC_END_DT	Use the srvc_end_dt for derving quarter baased on type of qtr (State, Federal or calendar) . Date - Jul 1, 2017 = 2018Q1
SVC_END_DT	Use the srvc_end_dt for derving quarter baased on type of qtr (State, Federal or calendar). Date Oct 1 2017 = 2018Q1
SVC_END_DT	Use the srvc_end_dt for derving quarter baased on type of qtr (State, Federal or calendar) . Date Jan 1 2018 = 2018Q1
CLM_HDR_PD_DT	Use the paid date for derving quarter baased on type of qtr (State, Federal or calendar) . Date - Jul 1, 2017 = 2018Q1
CLM_HDR_PD_DT	Use the paid_dt for derving quarter baased on type of qtr (State, Federal or calendar). Date Oct 1 2017 = 2018Q1
CLM_HDR_PD_DT	Use the paid_dt for derving quarter baased on type of qtr (State, Federal or calendar) . Date Jan 1 2018 = 2018Q1

Re: Change into Quarter

PGStats — Fri, 09 Mar 2018 05:29:00 GMT

Please define the types of qtr. Where would you find which type to use?

Re: Change into Quarter

Ahsan — Fri, 09 Mar 2018 05:31:17 GMT

Here is the type

SVC_SFY_QTR

SVC_FFY_QTR

SVC_CY_QTR

PAID_SFY_QTR

PAID_FFY_QTR

PAID_CY_QTR

Re: Change into Quarter

PGStats — Fri, 09 Mar 2018 05:41:58 GMT

Please define the types of qtr (State, Federal, calendar). What would these _QTR variables contain?

Re: Change into Quarter

Ahsan — Fri, 09 Mar 2018 05:53:24 GMT

SVC_SFY_QTR= State Fiscal Year Use the srvc_end_dt for derving quarter baased on type of qtr (State, Federal or calendar) . Date - Jul 1, 2017 = 2018Q1

SVC_FFY_QTR= State Federal Fiscal Year Use the srvc_end_dt for derving quarter baased on type of qtr (State, Federal or calendar). Date Oct 1 2017 = 2018Q1

SVC_CY_QTR= State Calendar Year Use the srvc_end_dt for derving quarter baased on type of qtr (State, Federal or calendar) . Date Jan 1 2018 = 2018Q1

PAID_SFY_QTR= State Fiscal Year Use the paid date for derving quarter baased on type of qtr (State, Federal or calendar) . Date - Jul 1, 2017 = 2018Q1

PAID_FFY_QTR=State Federal Fiscal Year Use the paid_dt for derving quarter baased on type of qtr (State, Federal or calendar). Date Oct 1 2017 = 2018Q1

PAID_CY_QTR= State Calendar Year Use the paid_dt for derving quarter baased on type of qtr (State, Federal or calendar) . Date Jan 1 2018 = 2018Q1

Re: Change into Quarter

PGStats — Fri, 09 Mar 2018 06:20:53 GMT

Use the intnx function with the appropriate interval and shift. Have your pick:

data test;
do year = 2017, 2018;
    do month = 1 to 12;
        date = mdy(month,1,year);
        q1 = intnx("QTR3.1", date, 0);        
        q2 = intnx("QTR3.2", date, 0);        
        q3 = intnx("QTR3.3", date, 0);
        q4 = intnx("QTR3.4", date, 0);
        q1_1 = intnx("QTR3.1", date, 1);        
        q2_1 = intnx("QTR3.2", date, 1);        
        q3_1 = intnx("QTR3.3", date, 1);
        q4_1 = intnx("QTR3.4", date, 1);
        output;
        end;
    end;
format date yymmdd10. q: yyq.;
drop year month;
run;

proc print noobs; run;

       date     q1       q2       q3       q4      q1_1     q2_1     q3_1     q4_1

 2017-01-01   2017Q1   2016Q2   2016Q2   2016Q3   2017Q4   2017Q1   2017Q1   2017Q2
 2017-02-01   2017Q1   2017Q1   2016Q2   2016Q3   2017Q4   2017Q4   2017Q1   2017Q2
 2017-03-01   2017Q1   2017Q1   2017Q1   2016Q3   2017Q4   2017Q4   2017Q4   2017Q2
 2017-04-01   2017Q1   2017Q1   2017Q1   2017Q2   2017Q4   2017Q4   2017Q4   2018Q1
 2017-05-01   2017Q1   2017Q1   2017Q1   2017Q2   2017Q4   2017Q4   2017Q4   2018Q1
 2017-06-01   2017Q1   2017Q1   2017Q1   2017Q2   2017Q4   2017Q4   2017Q4   2018Q1
 2017-07-01   2017Q1   2017Q1   2017Q1   2017Q2   2017Q4   2017Q4   2017Q4   2018Q1
 2017-08-01   2017Q1   2017Q1   2017Q1   2017Q2   2017Q4   2017Q4   2017Q4   2018Q1
 2017-09-01   2017Q1   2017Q1   2017Q1   2017Q2   2017Q4   2017Q4   2017Q4   2018Q1
 2017-10-01   2017Q4   2017Q1   2017Q1   2017Q2   2018Q3   2017Q4   2017Q4   2018Q1
 2017-11-01   2017Q4   2017Q4   2017Q1   2017Q2   2018Q3   2018Q3   2017Q4   2018Q1
 2017-12-01   2017Q4   2017Q4   2017Q4   2017Q2   2018Q3   2018Q3   2018Q3   2018Q1
 2018-01-01   2017Q4   2017Q4   2017Q4   2018Q1   2018Q3   2018Q3   2018Q3   2018Q4
 2018-02-01   2017Q4   2017Q4   2017Q4   2018Q1   2018Q3   2018Q3   2018Q3   2018Q4
 2018-03-01   2017Q4   2017Q4   2017Q4   2018Q1   2018Q3   2018Q3   2018Q3   2018Q4
 2018-04-01   2017Q4   2017Q4   2017Q4   2018Q1   2018Q3   2018Q3   2018Q3   2018Q4
 2018-05-01   2017Q4   2017Q4   2017Q4   2018Q1   2018Q3   2018Q3   2018Q3   2018Q4
 2018-06-01   2017Q4   2017Q4   2017Q4   2018Q1   2018Q3   2018Q3   2018Q3   2018Q4
 2018-07-01   2018Q3   2017Q4   2017Q4   2018Q1   2019Q2   2018Q3   2018Q3   2018Q4
 2018-08-01   2018Q3   2018Q3   2017Q4   2018Q1   2019Q2   2019Q2   2018Q3   2018Q4
 2018-09-01   2018Q3   2018Q3   2018Q3   2018Q1   2019Q2   2019Q2   2019Q2   2018Q4
 2018-10-01   2018Q3   2018Q3   2018Q3   2018Q4   2019Q2   2019Q2   2019Q2   2019Q3
 2018-11-01   2018Q3   2018Q3   2018Q3   2018Q4   2019Q2   2019Q2   2019Q2   2019Q3
 2018-12-01   2018Q3   2018Q3   2018Q3   2018Q4   2019Q2   2019Q2   2019Q2   2019Q3

Re: Change into Quarter

Ahsan — Fri, 09 Mar 2018 13:32:50 GMT

do you think it's a right format for the quarters

for SVC_SFY_QTR

INTNX('QTR', 't1.SVC_END_DT', 1)

Date - Jul 1, 2017 = 2018Q1

is it right approach

Re: Change into Quarter

Ahsan — Fri, 09 Mar 2018 21:44:44 GMT

Please can somebody help me out with date issue.

Re: Change into Quarter

Patrick — Fri, 09 Mar 2018 22:17:52 GMT

Have you tried to understand the code @PGStats posted? There isn't much to add to this.

If you're still struggling: Post representative sample data (in the form of a working SAS data step creating this data) and then tell us how the desired result should look like based on the sample data posted.

Re: Change into Quarter

Ahsan — Sat, 10 Mar 2018 16:09:43 GMT

DBA send me his code

(case when to_char(ch.SVC_END_DT, 'MM') > '06' and to_char(ch.SVC_END_DT, 'MM') < '10' then to_char(to_number(to_char(ch.SVC_END_DT,'YYYY')) + 1)||'Q1'
      when to_char(ch.SVC_END_DT, 'MM') > '09' then to_char(to_number(to_char(ch.SVC_END_DT,'YYYY')) + 1)||'Q2'
      when to_char(ch.SVC_END_DT, 'MM') < '04' then to_char(to_number(to_char(ch.SVC_END_DT,'YYYY')))||'Q3'
      when to_char(ch.SVC_END_DT, 'MM') > '03' and to_char(ch.SVC_END_DT, 'MM') < '07' then to_char(to_number(to_char(ch.SVC_END_DT,'YYYY')))||'Q4'
   else null end) SVC_SFY_QTR

how do I change it into SAS and validate. All the help appreciated.

Re: Change into Quarter

Ahsan — Sat, 10 Mar 2018 20:01:09 GMT

Can somebody help me out with this code how do I convert into SAS.

Thank You

Re: Change into Quarter

Patrick — Sun, 11 Mar 2018 00:42:02 GMT

@Ahsan

What is it that you don't understand? The SAS code @PGStats already posted or the Oracle SQL the DBA shared with you?

Going forward you will need to spend a bit more time to formulate targeted questions. Just "help me out" could be understood as "just do all the work for me" and that's not what this forum is for.

But o.k. it's only your second post here. @PGStats actually provides already the SAS solution (you just need to pick the appropriate case) but here another variant on how to implement in SAS the logic from the Oracle SQL.

data sample;
  input SVC_END_DT anydtdtm.;
  format SVC_END_DT datetime21.;
  SVC_SFY_QTR=put(intnx('quarter',datepart(SVC_END_DT),+2,'s'),yyq6.);
datalines;
15jan2017
31mar2017
01apr2017
30jun2017
01jul2017
30sep2017
15oct2017
31dec2017
01jan2018
;
run;