BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Ahsan
Calcite | Level 5

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

@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

11 REPLIES 11
PGStats
Opal | Level 21

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

PG
Ahsan
Calcite | Level 5

Here is the type

SVC_SFY_QTR
SVC_FFY_QTR
SVC_CY_QTR
PAID_SFY_QTR
PAID_FFY_QTR
PAID_CY_QTR
PGStats
Opal | Level 21

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

PG
Ahsan
Calcite | Level 5
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
 
 
 
 
 
 
PGStats
Opal | Level 21

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
Ahsan
Calcite | Level 5

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

Ahsan
Calcite | Level 5

Please can somebody help me out with date issue.

Patrick
Opal | Level 21

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.

Ahsan
Calcite | Level 5

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. 

Ahsan
Calcite | Level 5
Can somebody help me out with this code how do I convert into SAS.

Thank You
Patrick
Opal | Level 21

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

 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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