DATA Step, Macro, Functions and more

Combine Column using CASE WHEN in SQL

Accepted Solution Solved
Reply
Contributor
Posts: 47
Accepted Solution

Combine Column using CASE WHEN in SQL

Monthyear
12015
22015
32016
42016
52015
62016
72015
82016
92015
102014
112015
122016

 

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)

MonthyearQTRyearQTR
1201512015Q1
2201512015Q1
3201612016Q1
4201622016Q1
5201522015Q2
6201622016Q2
7201532015Q3
8201632016Q3
9201532015Q3
10201442014Q4
11201542015Q4
12201642016Q4

 

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;


Accepted Solutions
Solution
‎07-12-2016 02:13 AM
Super User
Posts: 19,772

Re: Combine Column using CASE WHEN in SQL

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

View solution in original post


All Replies
Super User
Posts: 5,498

Re: Combine Column using CASE WHEN in SQL

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;

Solution
‎07-12-2016 02:13 AM
Super User
Posts: 19,772

Re: Combine Column using CASE WHEN in SQL

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 topic is solved.

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

Discussion stats
  • 2 replies
  • 234 views
  • 2 likes
  • 3 in conversation