topic Re: Create quarter as per month in SAS Programming

Create quarter as per month

subrat1 — Thu, 14 Jul 2016 20:55:01 GMT

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

Re: Create quarter as per month

Reeza — Thu, 14 Jul 2016 20:57:55 GMT

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

Re: Create quarter as per month

ballardw — Thu, 14 Jul 2016 22:12:39 GMT

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

Re: Create quarter as per month

subrat1 — Fri, 15 Jul 2016 01:13:56 GMT

Yes quarter is last month of group of three

Re: Create quarter as per month

Ksharp — Fri, 15 Jul 2016 01:19:55 GMT

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;

Re: Create quarter as per month

Astounding — Fri, 15 Jul 2016 01:44:22 GMT

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.

Re: Create quarter as per month

Reeza — Fri, 15 Jul 2016 02:47:06 GMT

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

Re: Create quarter as per month

Astounding — Fri, 15 Jul 2016 11:58:52 GMT

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;

Re: Create quarter as per month

Reeza — Fri, 15 Jul 2016 14:02:20 GMT

True...or change to 2.5 in addition.

floor(month+2.5)