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 |
Your logic isn't clear. Please clarify your question.
It looks like your "quarter" variable is last "month" of a group of three. Correct?
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;
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.
Quarter = floor((month + 3)/3)*3;
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;
True...or change to 2.5 in addition.
floor(month+2.5)
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.