BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
subrat1
Fluorite | Level 6
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;

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

2 REPLIES 2
Astounding
PROC Star

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
Super User

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

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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