DATA Step, Macro, Functions and more

Create quarter as per month

Reply
Contributor
Posts: 47

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:

MonthQuarter
46
56
66
13
23
33
79
89
99
1315
1415
1515
1012
1112
1212
1618
1718
1818
4345
4445
4545
119120
118120
120120
6060
5860
5760
8990
8890
9090
180180
178180
179180
191192
192192
190192

 

 

Super User
Posts: 19,770

Re: Create quarter as per month

Your logic isn't clear. Please clarify your question. 

 

Super User
Posts: 11,343

Re: Create quarter as per month

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

Contributor
Posts: 47

Re: Create quarter as per month

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

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: 5,497

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: 19,770

Re: Create quarter as per month

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

 

 

Super User
Posts: 5,497

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: 19,770

Re: Create quarter as per month

Posted in reply to Astounding

True...or change to 2.5 in addition.

 

floor(month+2.5) 

 

 

 

Ask a Question
Discussion stats
  • 8 replies
  • 456 views
  • 4 likes
  • 5 in conversation