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 |
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;
Please define the types of qtr. Where would you find which type to use?
Here is the type
SVC_SFY_QTR |
SVC_FFY_QTR |
SVC_CY_QTR |
PAID_SFY_QTR |
PAID_FFY_QTR |
PAID_CY_QTR |
Please define the types of qtr (State, Federal, calendar). What would these _QTR variables contain?
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 |
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
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
Please can somebody help me out with date issue.
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.
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.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.