topic Re: Combine Column using CASE WHEN in SQL in SAS Programming

Combine Column using CASE WHEN in SQL

subrat1 — Mon, 11 Jul 2016 20:17:41 GMT

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)

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;

Astounding — Mon, 11 Jul 2016 20:29:37 GMT

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;

Reeza — Mon, 11 Jul 2016 21:12:02 GMT

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