Contributor
Posts: 58

# Create quarter as per month

I want to create a macro. I have one column : month (from 2000 to 2015Q4 = 16 years * 4 quarters * 3 months per quarter = 192 months

plus 2016Q1,2016Q2 = 6 months

So as per my month column i want to create Quarter Column using macro as my month will keep increasing my quarter shoud increase.

for example:

 Month Quarter 4 6 5 6 6 6 1 3 2 3 3 3 7 9 8 9 9 9 13 15 14 15 15 15 10 12 11 12 12 12 16 18 17 18 18 18 43 45 44 45 45 45 119 120 118 120 120 120 60 60 58 60 57 60 89 90 88 90 90 90 180 180 178 180 179 180 191 192 192 192 190 192

Super User
Posts: 23,685

Super User
Posts: 13,512

## Re: Create quarter as per month

It looks like your "quarter" variable is last "month" of a group of three. Correct?

Contributor
Posts: 58

## Re: Create quarter as per month

Yes quarter is last month of group of three
Super User
Posts: 10,770

## Re: Create quarter as per month

[ Edited ]

Assuming I understand what you mean, and there is no gap in MONTH.

``````data have;
infile cards expandtabs;
input Month;
cards;
4	6
5	6
6	6
1	3
2	3
3	3
7	9
8	9
9	9
13	15
14	15
15	15
10	12
11	12
12	12
16	18
17	18
18	18
43	45
44	45
45	45
119	120
118	120
120	120
60	60
58	60
57	60
89	90
88	90
90	90
180	180
178	180
179	180
191	192
192	192
190
;
run;
data have;
set have;
if mod(_n_,3)=1 then group+1;
run;

proc sql;
select *,max(month) as Quarter
from have
group by group;
quit;``````
Super User
Posts: 6,756

## Re: Create quarter as per month

Another possibility:

data want;

do _n_=1 to 3 until (done1);

set have end=done1;

quarter = max(month, quarter);

end;

do _n_=1 to 3 until (done2);

set have end=done2;

output;

end;

run;

The UNTIL conditions are possibly overkill ... they allow for the possibility that you only have 1 or 2 months in the last group, rather than 3 months.

Super User
Posts: 23,685

## Re: Create quarter as per month

Quarter = floor((month + 3)/3)*3;

Super User
Posts: 6,756

## Re: Create quarter as per month

Reeza,

I'm not so sure that will work for the largest month of the three, but it gives rise to some interesting ideas.  This variation should work:

quarter = ceil(month/3) * 3;

Super User
Posts: 23,685

## Re: Create quarter as per month

True...or change to 2.5 in addition.

floor(month+2.5)

Discussion stats
• 8 replies
• 532 views
• 4 likes
• 5 in conversation