DATA Step, Macro, Functions and more

Change into Quarter

Accepted Solution Solved
Reply
Contributor
Posts: 60
Accepted Solution

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_DTUse the srvc_end_dt for derving quarter baased on type of qtr (State, Federal or calendar) . Date - Jul 1, 2017 = 2018Q1
SVC_END_DTUse the srvc_end_dt for derving quarter baased on type of qtr (State, Federal or calendar). Date Oct 1 2017 = 2018Q1
SVC_END_DTUse the srvc_end_dt for derving quarter baased on type of qtr (State, Federal or calendar) . Date Jan 1 2018 = 2018Q1
CLM_HDR_PD_DTUse the paid date for derving quarter baased on type of qtr (State, Federal or calendar) . Date - Jul 1, 2017 = 2018Q1
CLM_HDR_PD_DTUse the paid_dt for derving quarter baased on type of qtr (State, Federal or calendar). Date Oct 1 2017 = 2018Q1
CLM_HDR_PD_DTUse 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
Respected Advisor
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;

 

 

View solution in original post


All Replies
Esteemed Advisor
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
Esteemed Advisor
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
 
 
 
 
 
 
Esteemed Advisor
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.

Respected Advisor
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
Respected Advisor
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.

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

Discussion stats
  • 11 replies
  • 224 views
  • 0 likes
  • 3 in conversation