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

sas-innovate-2024.png

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.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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