Solved
Contributor
Posts: 60

Change into Quarter

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

Accepted Solutions
Solution
‎03-10-2018 08:29 PM
Posts: 4,779

Re: Change into Quarter

[ Edited ]

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

All Replies
Posts: 5,614

Re: Change into Quarter

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

PG
Contributor
Posts: 60

Re: Change into Quarter

Here is the type

 SVC_SFY_QTR SVC_FFY_QTR SVC_CY_QTR PAID_SFY_QTR PAID_FFY_QTR PAID_CY_QTR
Posts: 5,614

Re: Change into Quarter

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

PG
Contributor
Posts: 60

Re: Change into Quarter

[ Edited ]
 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
Posts: 5,614

Re: Change into Quarter

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
```
PG
Contributor
Posts: 60

Re: Change into Quarter

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

Contributor
Posts: 60

Re: Change into Quarter

Please can somebody help me out with date issue.

Posts: 4,779

Re: Change into Quarter

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.

Contributor
Posts: 60

Re: Change into Quarter

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.

Contributor
Posts: 60

Re: Change into Quarter

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

Thank You
Solution
‎03-10-2018 08:29 PM
Posts: 4,779

Re: Change into Quarter

[ Edited ]

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

☑ This topic is solved.