Month | year |
1 | 2015 |
2 | 2015 |
3 | 2016 |
4 | 2016 |
5 | 2015 |
6 | 2016 |
7 | 2015 |
8 | 2016 |
9 | 2015 |
10 | 2014 |
11 | 2015 |
12 | 2016 |
I want to create two new column from this table: 1: QTR 2: yearQTR
So that my final table shoud look like as given below: (all column should be numeric)
Month | year | QTR | yearQTR |
1 | 2015 | 1 | 2015Q1 |
2 | 2015 | 1 | 2015Q1 |
3 | 2016 | 1 | 2016Q1 |
4 | 2016 | 2 | 2016Q1 |
5 | 2015 | 2 | 2015Q2 |
6 | 2016 | 2 | 2016Q2 |
7 | 2015 | 3 | 2015Q3 |
8 | 2016 | 3 | 2016Q3 |
9 | 2015 | 3 | 2015Q3 |
10 | 2014 | 4 | 2014Q4 |
11 | 2015 | 4 | 2015Q4 |
12 | 2016 | 4 | 2016Q4 |
Please help in coding,
i have one code for this but its not working, the error that i am getting is: Concatenation need character operand
SELECT
T1.*,
CASE
WHEN MONTH_CT IN (1,2,3) THEN 1
WHEN MONTH_CT IN (4,5,6) THEN 2
WHEN MONTH_CT IN (7,8,9) THEN 3
ELSE 4
END AS QTR,
YEAR_CT||
'Q'||
CASE
WHEN MONTH_CT IN (1,2,3) THEN 1
WHEN MONTH_CT IN (4,5,6) THEN 2
WHEN MONTH_CT IN (7,8,9) THEN 3
ELSE 4
END
AS YQTR
FROM CURRENT.AGG_RISK_COV T1;
QUIT;
Use a format and/or function.
This will work in either a data step or SQL, with appropriate syntax.
Put(mdy(month, 1, year), yyq10.) as yearQTR
Qtr(mdy(month, 1, year)) as QTR
This would be so easy in a DATA step. Is there any reason you need to use SQL?
data want;
set current.agg_risk_cov;
qtr = ceil(month_ct/3);
yqtr = put(year, 4.) || 'Q' || put(qtr, 1.);
run;
Use a format and/or function.
This will work in either a data step or SQL, with appropriate syntax.
Put(mdy(month, 1, year), yyq10.) as yearQTR
Qtr(mdy(month, 1, year)) as QTR
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.
Ready to level-up your skills? Choose your own adventure.