topic Re: Summing over certain time periods/intervals in SAS Programming

Summing over certain time periods/intervals

mexes — Fri, 19 Apr 2013 14:48:44 GMT

I have a problem. Can somebody show me how I can compute net fund flows over a 3 months, 6 months and 12 months period.

As variables I have the number of shares (shares), the transaction price (tprice), the transaction code (trancode) and the transaction date (trandate).

I already calculated the fund flows. The fund flow is positive if it s a purchase of shares and the fund flow is negative if it is a sale of the shares.

Now I have the fund flows for different transaction dates. Summing up these fund flows gives me the net fund flows over my whole time period. But I am interested in the net fund flows over 3 months, 6 months and 12 months. Can somebody show me how to handle this problem. I tried different codes/formulas but nothing seems to work correctly.

I also attached the data called "example1".

Thank you for your help.

Re: Summing over certain time periods/intervals

Reeza — Fri, 19 Apr 2013 15:25:47 GMT

What do you want your output to look like?

Personally, I prefer if you include your data as a data step, easier to copy and paste rather than a full data set that has to be downloaded and then linked to

Re: Summing over certain time periods/intervals

mexes — Fri, 19 Apr 2013 16:01:35 GMT

I would like to have the net fund flow in a column next to the fund_flow column as a number.

data mylib.example1;

set mylib.example;

fund_flow=tprice*shares;

if trancode='S' then fund_flow=-fund_flow;

run;

proc print data=mylib.exampleq;

run;

Obs	Cusip	cusip2	trancode	trandate	tprice	shares	cleanse	fund_flow
1	060505	10	S	1990/01/23	43.63	5000.0000	R	-218150.00
2	060505	10	S	1990/04/27	37.00	5000.0000	R	-185000.00
3	060505	10	P	1990/06/18	39.00	500.0000	R	19500.00
4	060505	10	S	1990/07/19	37.13	3000.0000	R	-111390.00
5	060505	10	P	1990/08/03	34.63	500.0000	R	17315.00
6	060505	10	P	1990/09/25	25.13	200.0000	R	5026.00
7	060505	10	P	1990/09/25	25.00	300.0000	R	7500.00
8	060505	10	P	1990/09/27	19.63	10000.0000	R	196300.00
9	060505	10	P	1990/10/04	20.50	200.0000	R	4100.00
10	060505	10	P	1990/10/09	20.50	300.0000	R	6150.00
11	060505	10	P	1990/10/16	19.13	5000.0000	R	95650.00
12	060505	10	P	1990/10/23	19.25	1000.0000	R	19250.00
13	060505	10	P	1990/10/23	19.38	1000.0000	R	19380.00
14	060505	10	P	1990/10/24	18.50	1000.0000	R	18500.00
15	060505	10	P	1990/11/01	18.50	1000.0000	H	18500.00
16	060505	10	P	1990/11/02	19.79	100000.0000	R	1979000.00
17	060505	10	P	1990/11/05	19.27	1000.0000	R	19270.00
18	060505	10	P	1990/11/06	20.04	6700.0000	R	134268.00
19	060505	10	P	1990/11/07	20.38	1200.0000	R	24456.00
20	060505	10	P	1990/11/07	20.67	100000.0000	R	2067000.00
21	060505	10	P	1990/11/07	20.35	178400.0000	R	3630440.00
22	060505	10	P	1990/11/08	21.49	48600.0000	R	1044414.00
23	060505	10	P	1990/11/08	21.23	182600.0000	R	3876598.00
24	060505	10	P	1990/11/09	21.48	50000.0000	R	1074000.00
25	060505	10	P	1990/11/09	21.36	85000.0000	R	1815600.00
26	060505	10	P	1990/11/12	22.00	1000.0000	R	22000.00
27	060505	10	P	1990/11/12	23.01	152100.0000	R	3499821.00
28	060505	10	P	1990/11/13	23.92	25000.0000	R	598000.00
29	060505	10	P	1990/11/13	23.72	365100.0000	R	8660172.00
30	060505	10	P	1990/11/14	24.92	100000.0000	R	2492000.00
31	060505	10	P	1990/11/14	24.69	515600.0000	R	12730164.00
32	060505	10	P	1990/11/15	23.42	67200.0000	R	1573824.00
33	060505	10	P	1990/11/15	23.60	323700.0000	R	7639320.00
34	060505	10	P	1990/11/15	23.67	175000.0000	R	4142250.00
35	060505	10	P	1990/11/16	24.02	391200.0000	R	9396624.00
36	060505	10	P	1990/11/19	24.79	25000.0000	R	619750.00
37	060505	10	P	1990/11/19	25.04	75000.0000	R	1878000.00
38	060505	10	P	1990/11/19	25.00	223800.0000	R	5595000.00
39	060505	10	P	1990/11/20	24.52	691300.0000	R	16950676.00
40	060505	10	P	1990/11/21	24.34	518700.0000	R	12625158.00
41	060505	10	P	1990/11/26	22.75	500.0000	R	11375.00
42	060505	10	S	1990/12/04	24.00	8480.0000	R	-203520.00
43	060505	10	P	1990/12/31	23.43	700.0000	R	16401.00
44	060505	10	P	1991/01/15	22.00	94.0000	R	2068.00
45	060505	10	S	1991/01/21	24.28	11000.0000	R	-267080.00
46	060505	10	S	1991/01/30	28.25	5365.0000	R	-151561.25
47	060505	10	P	1991/07/23	34.75	50000.0000	R	1737500.00
48	060505	10	P	1991/07/24	34.75	1000.0000	R	34750.00
49	060505	10	P	1991/07/25	34.63	500.0000	R	17315.00
50	060505	10	P	1991/07/29	34.00	4500.0000	R	153000.00
51	060505	10	P	1991/07/31	34.63	4000.0000	R	138520.00
52	060505	10	S	1991/11/05	37.50	9600.0000	R	-360000.00
53	060505	10	S	1991/11/20	35.38	6000.0000	R	-212280.00
54	060505	10	P	1991/11/20	35.38	500.0000	R	17690.00
55	060505	10	P	1992/03/10	45.38	500.0000	R	22690.00
56	060505	10	P	1992/04/24	49.00	500.0000	R	24500.00
57	060505	10	S	1992/04/24	49.00	116573.0000	R	-5712077.00
58	060505	10	S	1992/05/08	48.50	1000.0000	R	-48500.00
59	060505	10	S	1992/05/21	48.50	25000.0000	R	-1212500.00
60	060505	10	P	1992/06/17	46.50	9017.0000	R	419290.50
61	060505	10	S	1992/06/17	46.50	9017.0000	R	-419290.50
62	060505	10	S	1992/08/18	47.00	21650.0000	R	-1017550.00
63	060505	10	P	1992/10/21	45.25	500.0000	R	22625.00
64	060505	10	S	1992/11/04	47.13	423.0000	R	-19935.99
65	060505	10	S	1992/11/05	47.00	19700.0000	R	-925900.00
66	060505	10	S	1992/11/05	47.13	5600.0000	R	-263928.00
67	060505	10	S	1993/03/31	55.00	3655.0000	R	-201025.00
68	060505	10	P	1993/04/20	54.63	1000.0000	R	54630.00
69	060505	10	S	1993/04/30	50.50	10000.0000	R	-505000.00
70	060505	10	S	1993/05/04	49.50	10000.0000	R	-495000.00
71	060505	10	S	1993/05/04	50.38	10000.0000	R	-503800.00
72	060505	10	S	1993/05/04	50.63	12400.0000	R	-627812.00
73	060505	10	P	1993/05/06	49.00	500.0000	R	24500.00
74	060505	10	P	1993/05/18	46.38	500.0000	R	23190.00
75	060505	10	P	1993/10/27	47.00	500.0000	R	23500.00
76	060505	10	P	1994/02/16	49.13	300.0000	R	14739.00
77	060505	10	P	1994/03/08	46.88	1500.0000	R	70320.00
78	060505	10	P	1994/05/05	52.88	174.0000	R	9201.12
79	060505	10	P	1994/06/15	55.38	10716.0000	R	593452.08
80	060505	10	S	1994/06/15	55.38	10716.0000	R	-593452.08
81	060505	10	P	1994/07/19	55.00	121.0000	R	6655.00
82	060505	10	P	1994/07/19	55.00	221.0000	R	12155.00
83	060505	10	P	1994/11/17	46.50	1000.0000	R	46500.00
84	060505	10	P	1994/11/23	44.88	5000.0000	R	224400.00
85	060505	10	P	1996/01/19	66.63	1000.0000	R	66630.00
86	060505	10	S	1996/02/23	37.31	1100.0000	H	-41041.00
87	060505	10	S	1996/02/23	37.31	2200.0000	H	-82082.00
88	060505	10	P	1996/03/26	38.50	1500.0000	H	57750.00
89	060505	10	P	1996/04/04	41.13	1100.0000	L	45243.00
90	060505	10	S	1996/04/17	76.52	20000.0000	R	-1530400.00
91	060505	10	S	1996/04/17	76.52	20000.0000	R	-1530400.00
92	060505	10	S	1996/04/29	79.00	1700.0000	R	-134300.00
93	060505	10	S	1996/04/29	79.00	1700.0000	R	-134300.00
94	060505	10	S	1996/04/30	79.00	8300.0000	R	-655700.00
95	060505	10	S	1996/04/30	79.00	8300.0000	R	-655700.00
96	060505	10	S	1996/06/26	40.13	1900.0000	H	-76247.00
97	060505	10	P	1996/07/16	39.56	80.0000	H	3164.80
98	060505	10	P	1996/07/16	79.38	1000.0000	R	79380.00
99	060505	10	S	1996/07/24	41.56	6359.0000	H	-264280.04
100	060505	10	S	1996/07/24	41.56	31971.0000	H	-1328714.76
101	060505	10	S	1996/07/24	41.56	26667.0000	H	-1108280.52
102	060505	10	S	1996/08/01	43.00	4000.0000	H	-172000.00
103	060505	10	S	1996/08/14	45.31	1313.0000	H	-59492.03
104	060505	10	S	1996/08/14	45.31	2400.0000	H	-108744.00
105	060505	10	S	1996/08/14	90.38	1313.0000	R	-118668.94
106	060505	10	S	1996/08/14	90.25	2400.0000	R	-216600.00
107	060505	10	P	1996/09/03	42.00	1000.0000	H	42000.00
108	060505	10	P	1996/09/05	83.00	500.0000	R	41500.00
109	060505	10	P	1996/09/19	42.31	200.0000	H	8462.00
110	060505	10	S	1996/09/27	43.31	20000.0000	H	-866200.00
111	060505	10	S	1996/10/21	45.69	2000.0000	H	-91380.00
112	060505	10	P	1996/10/25	45.25	500.0000	H	22625.00
113	060505	10	S	1996/10/29	46.25	40750.0000	H	-1884687.50
114	060505	10	S	1997/01/13	51.88	300.0000	R	-15564.00
115	060505	10	S	1997/01/21	53.38	85000.0000	H	-4537300.00
116	060505	10	S	1997/01/21	53.38	160000.0000	H	-8540800.00
117	060505	10	S	1997/01/21	53.38	42482.0000	H	-2267689.16
118	060505	10	S	1997/01/22	54.81	13333.0000	H	-730781.73
119	060505	10	S	1997/02/05	55.19	80000.0000	H	-4415200.00
120	060505	10	S	1997/02/05	55.19	80000.0000	H	-4415200.00
121	060505	10	S	1997/02/06	55.63	400.0000	H	-22252.00
122	060505	10	S	1997/02/20	60.88	17000.0000	H	-1034960.00
123	060505	10	S	1997/02/21	60.13	300.0000	H	-18039.00
124	060505	10	P	1997/02/27	61.44	150.0000	R	9216.00
125	060505	10	S	1997/03/11	64.75	16000.0000	R	-1036000.00
126	060505	10	P	1997/03/12	63.38	1000.0000	R	63380.00
127	060505	10	S	1997/04/01	56.13	0.8819	H	-49.50
128	060505	10	S	1997/04/01	55.13	1804.5465	R	-99484.65
129	060505	10	S	1997/04/23	56.75	15000.0000	H	-851250.00
130	060505	10	S	1997/04/29	59.88	330.0000	H	-19760.40
131	060505	10	S	1997/04/29	59.88	600.0000	H	-35928.00
132	060505	10	S	1997/04/29	59.88	51954.0000	H	-3111005.52
133	060505	10	S	1997/04/30	60.38	2000.0000	H	-120760.00
134	060505	10	S	1997/05/01	60.38	15000.0000	H	-905700.00
135	060505	10	S	1997/05/01	60.38	40000.0000	H	-2415200.00
136	060505	10	S	1997/05/01	60.38	47500.0000	H	-2868050.00
137	060505	10	P	1997/05/16	60.75	1000.0000	R	60750.00
138	060505	10	P	1997/06/25	66.56	700.0000	R	46592.00
139	060505	10	P	1997/06/25	66.56	330.0000	R	21964.80
140	060505	10	S	1997/07/22	66.50	100000.0000	R	-6650000.00
141	060505	10	P	1997/07/22	66.94	500.0000	R	33470.00
142	060505	10	P	1997/08/27	64.90	400.0000	R	25960.00
143	060505	10	P	1997/08/27	66.47	20.0000	R	1329.40
144	060505	10	P	1997/09/02	64.13	715.0000	R	45852.95
145	060505	10	P	1997/09/03	59.25	1000.0000	R	59250.00
146	060505	10	P	1997/09/04	58.96	35.0000	R	2063.60
147	060505	10	P	1997/09/04	58.77	40.0000	R	2350.80
148	060505	10	P	1997/09/04	58.48	60.0000	R	3508.80
149	060505	10	P	1997/09/04	57.38	50.0000	R	2869.00
150	060505	10	P	1997/09/09	58.50	100.0000	R	5850.00
151	060505	10	S	1997/09/10	59.13	15913.0000	R	-940935.69
152	060505	10	P	1997/09/11	57.69	100.0000	R	5769.00
153	060505	10	P	1997/09/15	58.69	1000.0000	R	58690.00
154	060505	10	P	1997/09/22	61.13	1000.0000	H	61130.00
155	060505	10	P	1997/10/01	63.29	1680.7367	R	106373.83
156	060505	10	P	1997/10/27	58.13	1000.0000	R	58130.00
157	060505	10	P	1997/10/27	57.69	300.0000	H	17307.00
158	060505	10	S	1997/11/12	58.50	50.0000	R	-2925.00
159	060505	10	P	1998/01/13	58.06	2000.0000	R	116120.00
160	060505	10	S	1998/01/26	65.38	400000.0000	R	-26152000.00
161	060505	10	S	1998/01/30	71.18	66667.0000	R	-4745357.06
162	060505	10	S	1998/02/04	71.15	3600.0000	R	-256140.00
163	060505	10	P	1998/03/11	81.44	500.0000	R	40720.00
164	060505	10	P	1998/04/27	72.56	700.0000	R	50792.00
165	060505	10	P	1998/04/27	72.75	1000.0000	R	72750.00
166	060505	10	S	1998/06/10	86.88	4900.0000	R	-425712.00
167	060505	10	S	1998/07/14	87.83	24000.0000	R	-2107992.00
168	060505	10	S	1998/07/20	95.70	82970.0000	R	-7940229.00
169	060505	10	S	1998/07/20	95.25	50000.0000	R	-4762500.00
170	060505	10	S	1998/07/20	95.25	26668.0000	R	-2540127.00
171	060505	10	S	1998/07/20	95.25	33334.0000	R	-3175063.50
172	060505	10	S	1998/07/22	95.50	2000.0000	R	-191000.00
173	060505	10	S	1998/07/22	95.50	6904.0000	R	-659332.00
174	060505	10	S	1998/07/22	95.50	28000.0000	R	-2674000.00
175	060505	10	S	1998/07/28	80.00	2870.0000	R	-229600.00
176	060505	10	S	1998/07/28	80.00	20000.0000	R	-1600000.00
177	060505	10	P	1998/07/29	79.94	500.0000	R	39970.00
178	060505	10	S	1998/07/29	88.71	32000.0000	R	-2838720.00
179	060505	10	S	1998/07/30	90.50	7000.0000	R	-633500.00
180	060505	10	P	1998/08/14	78.50	1000.0000	R	78500.00
181	060505	10	P	1998/08/14	80.00	1000.0000	R	80000.00
182	060505	10	P	1998/09/01	63.38	1000.0000	R	63380.00
183	060505	10	P	1998/09/02	65.56	1000.0000	R	65560.00
184	060505	10	P	1998/10/15	46.69	1000.0000	R	46690.00
185	060505	10	P	1998/10/27	56.00	2000.0000	R	112000.00
186	060505	10	P	1998/12/02	64.88	2000.0000	R	129760.00
187	060505	10	P	1998/12/21	60.50	1000.0000	R	60500.00
188	060505	10	P	1999/02/19	65.31	1000.0000	R	65310.00
189	060505	10	S	1999/03/10	73.19	422.0000	R	-30886.18
190	060505	10	S	1999/04/20	73.14	424339.0000	R	-31036154.46
191	060505	10	S	1999/04/23	74.00	1600.0000	R	-118400.00
192	060505	10	P	1999/05/18	68.63	600.0000	R	41178.00
193	060505	10	P	1999/08/09	61.06	2000.0000	R	122120.00
194	060505	10	P	1999/08/09	61.13	500.0000	R	30565.00
195	060505	10	S	1999/08/27	64.38	20000.0000	R	-1287600.00
196	060505	10	S	1999/10/19	53.67	424361.0000	R	-22775454.87
197	060505	10	S	1999/11/23	63.63	60.0000	R	-3817.80
198	060505	10	P	1999/12/10	52.00	2000.0000	R	104000.00

The trandate has the format '01jan90'd. I hope that this is what you needed. Sorry I am a beginner in SAS.

Re: Summing over certain time periods/intervals

Reeza — Fri, 19 Apr 2013 16:18:29 GMT

What does the output look like? You say you want a new column I'm assuming you need 3, one for 3, 6, 12 month each? And how are those calculated, are they a rolling calculations or over specific 3/6/12 month periods starting at a specific time? Is it overall or by a certain stock or fund? Months can be different number of days as well, particularly the number of business days depending on the time of year.

Re: Summing over certain time periods/intervals

mexes — Fri, 19 Apr 2013 16:43:47 GMT

Yes, you are right. I need 3 columns, one for 3, 6 and 12 months. This would not be a rolling calculation, it would be a simple sum over the net fund flows for the first 3,6, 12 months and then for the next 3, 6, 12 months. The net fund flows are calculated as the sum of the fund flows, lets say for a 3 month period: then I would start at the 01.jan.90 and take the sum over the fund flows till 31.mar.90, then the same calculations for the next 3 month period from 01.apr.90 till 30.06.90 and so on. The calculation is only for a certain stock.

Many thanks for your help.

Re: Summing over certain time periods/intervals

Reeza — Fri, 19 Apr 2013 17:25:59 GMT

Then you don't really want 3 new columns unless you want the total for each observation as well?

This is where seeing your sample output would be useful.

For starters try the 3 month and annual one this way:

proc means data=mylib.exampleq;

class trandate;

format trandate yyq6.;

var fund_flow;

output out=summary_3_months sum(fund_flow)=fund_flow_3_months;

run;

proc means data=mylib.exampleq;

class trandate;

format trandate year4.;

var fund_flow;

output out=summary_12_months sum(fund_flow)=fund_flow_12_months;

run;

For the 6 month one you can make your own format or see if there's a predefined SAS one. I can't recall one off the top of my head.

If your doing this by stock you may need to add the stock into the class variable.

Re: Summing over certain time periods/intervals

mexes — Fri, 19 Apr 2013 17:51:15 GMT

Thank you for your answer, it worked. Do you know maybe if this could be done with interval functions like intnx? Could you give me an hint how to use this function with the sum function?

Re: Summing over certain time periods/intervals

Reeza — Fri, 19 Apr 2013 18:04:37 GMT

I don't think intnx is appropriate here, because you'd have to know your start date.

If you wanted you could do something like the following instead though:

data mylib.example1;

set mylib.example;

fund_flow=tprice*shares;

if trancode='S' then fund_flow=-fund_flow;

Period_3months=put(trandate, yyq6.);

Period_12months=year(trandate);

*calculate 6 month interval;

if qtr(trandate) in (1, 2) then period_6months=put(period_12months, 4.)||"P1";

else if qtr(trandate) in (3,4) then period_6months=put(period_12months, 4.)||"P2";

else period_6months="CHECK";

run;

proc means data=mylib.example1;

class period_3months period_6months period_12months;

types 1;

var fund_flow;

output out=summary sum(fund_flow)=fund_flow_summarized;

run;

Re: Summing over certain time periods/intervals

mexes — Fri, 19 Apr 2013 19:32:54 GMT

Thanks a lot!!

Re: Summing over certain time periods/intervals

mexes — Sat, 20 Apr 2013 09:43:29 GMT

Hi Reeza

When I use this code I get an error message for types 1:

ERROR 22-322: Syntax error, expecting one of the following: ein Name, ;, (.

ERROR 200-322: The symbol is not recognized and will be ignored.

This is the code which I used in SAS:

data mylib.example1;

set mylib.example;

fund_flow=tprice*shares;

if trancode='S' then fund_flow=-fund_flow;

Period_3months=put(trandate, yyq6.);

Period_12months=year(trandate);

*calculate 6 month interval;

if qtr(trandate) in (1, 2) then period_6months=put(period_12months, 4.)||"P1";

else if qtr(trandate) in (3,4) then period_6months=put(period_12months, 4.)||"P2";

else period_6months="CHECK";

run;

proc means data=mylib.example1;

class period_3months period_6months period_12months;

types 1;

var fund_flow;

output out=summary sum(fund_flow)=fund_flow_summarized;

run;

Best wishes

Re: Summing over certain time periods/intervals

Reeza — Sat, 20 Apr 2013 17:37:56 GMT

Use Ways 1 instead...I mix them up all the time. If that doesnt work, Remove it and check the results. It will give you various levels of result, so make sure you use the ones you need.