BookmarkSubscribeRSS Feed
subrat1
Fluorite | Level 6

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

 

 

8 REPLIES 8
Reeza
Super User

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

 

ballardw
Super User

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

subrat1
Fluorite | Level 6
Yes quarter is last month of group of three
Ksharp
Super User

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;
Astounding
PROC Star

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.

Reeza
Super User

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

 

 

Astounding
PROC Star

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;

Reeza
Super User

True...or change to 2.5 in addition.

 

floor(month+2.5) 

 

 

 

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
  • 8 replies
  • 1652 views
  • 4 likes
  • 5 in conversation