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