DATA Step, Macro, Functions and more

Summing over certain time periods/intervals

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 19
Accepted Solution

Summing over certain time periods/intervals

Hi

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.

Attachment

Accepted Solutions
Solution
‎04-19-2013 01:26 PM
Super User
Posts: 19,869

Re: Summing over certain time periods/intervals

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.

View solution in original post


All Replies
Super User
Posts: 19,869

Re: Summing over certain time periods/intervals

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 Smiley Happy

Occasional Contributor
Posts: 19

Re: Summing over certain time periods/intervals

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;

ObsCusipcusip2trancodetrandatetpricesharescleansefund_flow
106050510S1990/01/2343.635000.0000R-218150.00
206050510S1990/04/2737.005000.0000R-185000.00
306050510P1990/06/1839.00500.0000R19500.00
406050510S1990/07/1937.133000.0000R-111390.00
506050510P1990/08/0334.63500.0000R17315.00
606050510P1990/09/2525.13200.0000R5026.00
706050510P1990/09/2525.00300.0000R7500.00
806050510P1990/09/2719.6310000.0000R196300.00
906050510P1990/10/0420.50200.0000R4100.00
1006050510P1990/10/0920.50300.0000R6150.00
1106050510P1990/10/1619.135000.0000R95650.00
1206050510P1990/10/2319.251000.0000R19250.00
1306050510P1990/10/2319.381000.0000R19380.00
1406050510P1990/10/2418.501000.0000R18500.00
1506050510P1990/11/0118.501000.0000H18500.00
1606050510P1990/11/0219.79100000.0000R1979000.00
1706050510P1990/11/0519.271000.0000R19270.00
1806050510P1990/11/0620.046700.0000R134268.00
1906050510P1990/11/0720.381200.0000R24456.00
2006050510P1990/11/0720.67100000.0000R2067000.00
2106050510P1990/11/0720.35178400.0000R3630440.00
2206050510P1990/11/0821.4948600.0000R1044414.00
2306050510P1990/11/0821.23182600.0000R3876598.00
2406050510P1990/11/0921.4850000.0000R1074000.00
2506050510P1990/11/0921.3685000.0000R1815600.00
2606050510P1990/11/1222.001000.0000R22000.00
2706050510P1990/11/1223.01152100.0000R3499821.00
2806050510P1990/11/1323.9225000.0000R598000.00
2906050510P1990/11/1323.72365100.0000R8660172.00
3006050510P1990/11/1424.92100000.0000R2492000.00
3106050510P1990/11/1424.69515600.0000R12730164.00
3206050510P1990/11/1523.4267200.0000R1573824.00
3306050510P1990/11/1523.60323700.0000R7639320.00
3406050510P1990/11/1523.67175000.0000R4142250.00
3506050510P1990/11/1624.02391200.0000R9396624.00
3606050510P1990/11/1924.7925000.0000R619750.00
3706050510P1990/11/1925.0475000.0000R1878000.00
3806050510P1990/11/1925.00223800.0000R5595000.00
3906050510P1990/11/2024.52691300.0000R16950676.00
4006050510P1990/11/2124.34518700.0000R12625158.00
4106050510P1990/11/2622.75500.0000R11375.00
4206050510S1990/12/0424.008480.0000R-203520.00
4306050510P1990/12/3123.43700.0000R16401.00
4406050510P1991/01/1522.0094.0000R2068.00
4506050510S1991/01/2124.2811000.0000R-267080.00
4606050510S1991/01/3028.255365.0000R-151561.25
4706050510P1991/07/2334.7550000.0000R1737500.00
4806050510P1991/07/2434.751000.0000R34750.00
4906050510P1991/07/2534.63500.0000R17315.00
5006050510P1991/07/2934.004500.0000R153000.00
5106050510P1991/07/3134.634000.0000R138520.00
5206050510S1991/11/0537.509600.0000R-360000.00
5306050510S1991/11/2035.386000.0000R-212280.00
5406050510P1991/11/2035.38500.0000R17690.00
5506050510P1992/03/1045.38500.0000R22690.00
5606050510P1992/04/2449.00500.0000R24500.00
5706050510S1992/04/2449.00116573.0000R-5712077.00
5806050510S1992/05/0848.501000.0000R-48500.00
5906050510S1992/05/2148.5025000.0000R-1212500.00
6006050510P1992/06/1746.509017.0000R419290.50
6106050510S1992/06/1746.509017.0000R-419290.50
6206050510S1992/08/1847.0021650.0000R-1017550.00
6306050510P1992/10/2145.25500.0000R22625.00
6406050510S1992/11/0447.13423.0000R-19935.99
6506050510S1992/11/0547.0019700.0000R-925900.00
6606050510S1992/11/0547.135600.0000R-263928.00
6706050510S1993/03/3155.003655.0000R-201025.00
6806050510P1993/04/2054.631000.0000R54630.00
6906050510S1993/04/3050.5010000.0000R-505000.00
7006050510S1993/05/0449.5010000.0000R-495000.00
7106050510S1993/05/0450.3810000.0000R-503800.00
7206050510S1993/05/0450.6312400.0000R-627812.00
7306050510P1993/05/0649.00500.0000R24500.00
7406050510P1993/05/1846.38500.0000R23190.00
7506050510P1993/10/2747.00500.0000R23500.00
7606050510P1994/02/1649.13300.0000R14739.00
7706050510P1994/03/0846.881500.0000R70320.00
7806050510P1994/05/0552.88174.0000R9201.12
7906050510P1994/06/1555.3810716.0000R593452.08
8006050510S1994/06/1555.3810716.0000R-593452.08
8106050510P1994/07/1955.00121.0000R6655.00
8206050510P1994/07/1955.00221.0000R12155.00
8306050510P1994/11/1746.501000.0000R46500.00
8406050510P1994/11/2344.885000.0000R224400.00
8506050510P1996/01/1966.631000.0000R66630.00
8606050510S1996/02/2337.311100.0000H-41041.00
8706050510S1996/02/2337.312200.0000H-82082.00
8806050510P1996/03/2638.501500.0000H57750.00
8906050510P1996/04/0441.131100.0000L45243.00
9006050510S1996/04/1776.5220000.0000R-1530400.00
9106050510S1996/04/1776.5220000.0000R-1530400.00
9206050510S1996/04/2979.001700.0000R-134300.00
9306050510S1996/04/2979.001700.0000R-134300.00
9406050510S1996/04/3079.008300.0000R-655700.00
9506050510S1996/04/3079.008300.0000R-655700.00
9606050510S1996/06/2640.131900.0000H-76247.00
9706050510P1996/07/1639.5680.0000H3164.80
9806050510P1996/07/1679.381000.0000R79380.00
9906050510S1996/07/2441.566359.0000H-264280.04
10006050510S1996/07/2441.5631971.0000H-1328714.76
10106050510S1996/07/2441.5626667.0000H-1108280.52
10206050510S1996/08/0143.004000.0000H-172000.00
10306050510S1996/08/1445.311313.0000H-59492.03
10406050510S1996/08/1445.312400.0000H-108744.00
10506050510S1996/08/1490.381313.0000R-118668.94
10606050510S1996/08/1490.252400.0000R-216600.00
10706050510P1996/09/0342.001000.0000H42000.00
10806050510P1996/09/0583.00500.0000R41500.00
10906050510P1996/09/1942.31200.0000H8462.00
11006050510S1996/09/2743.3120000.0000H-866200.00
11106050510S1996/10/2145.692000.0000H-91380.00
11206050510P1996/10/2545.25500.0000H22625.00
11306050510S1996/10/2946.2540750.0000H-1884687.50
11406050510S1997/01/1351.88300.0000R-15564.00
11506050510S1997/01/2153.3885000.0000H-4537300.00
11606050510S1997/01/2153.38160000.0000H-8540800.00
11706050510S1997/01/2153.3842482.0000H-2267689.16
11806050510S1997/01/2254.8113333.0000H-730781.73
11906050510S1997/02/0555.1980000.0000H-4415200.00
12006050510S1997/02/0555.1980000.0000H-4415200.00
12106050510S1997/02/0655.63400.0000H-22252.00
12206050510S1997/02/2060.8817000.0000H-1034960.00
12306050510S1997/02/2160.13300.0000H-18039.00
12406050510P1997/02/2761.44150.0000R9216.00
12506050510S1997/03/1164.7516000.0000R-1036000.00
12606050510P1997/03/1263.381000.0000R63380.00
12706050510S1997/04/0156.130.8819H-49.50
12806050510S1997/04/0155.131804.5465R-99484.65
12906050510S1997/04/2356.7515000.0000H-851250.00
13006050510S1997/04/2959.88330.0000H-19760.40
13106050510S1997/04/2959.88600.0000H-35928.00
13206050510S1997/04/2959.8851954.0000H-3111005.52
13306050510S1997/04/3060.382000.0000H-120760.00
13406050510S1997/05/0160.3815000.0000H-905700.00
13506050510S1997/05/0160.3840000.0000H-2415200.00
13606050510S1997/05/0160.3847500.0000H-2868050.00
13706050510P1997/05/1660.751000.0000R60750.00
13806050510P1997/06/2566.56700.0000R46592.00
13906050510P1997/06/2566.56330.0000R21964.80
14006050510S1997/07/2266.50100000.0000R-6650000.00
14106050510P1997/07/2266.94500.0000R33470.00
14206050510P1997/08/2764.90400.0000R25960.00
14306050510P1997/08/2766.4720.0000R1329.40
14406050510P1997/09/0264.13715.0000R45852.95
14506050510P1997/09/0359.251000.0000R59250.00
14606050510P1997/09/0458.9635.0000R2063.60
14706050510P1997/09/0458.7740.0000R2350.80
14806050510P1997/09/0458.4860.0000R3508.80
14906050510P1997/09/0457.3850.0000R2869.00
15006050510P1997/09/0958.50100.0000R5850.00
15106050510S1997/09/1059.1315913.0000R-940935.69
15206050510P1997/09/1157.69100.0000R5769.00
15306050510P1997/09/1558.691000.0000R58690.00
15406050510P1997/09/2261.131000.0000H61130.00
15506050510P1997/10/0163.291680.7367R106373.83
15606050510P1997/10/2758.131000.0000R58130.00
15706050510P1997/10/2757.69300.0000H17307.00
15806050510S1997/11/1258.5050.0000R-2925.00
15906050510P1998/01/1358.062000.0000R116120.00
16006050510S1998/01/2665.38400000.0000R-26152000.00
16106050510S1998/01/3071.1866667.0000R-4745357.06
16206050510S1998/02/0471.153600.0000R-256140.00
16306050510P1998/03/1181.44500.0000R40720.00
16406050510P1998/04/2772.56700.0000R50792.00
16506050510P1998/04/2772.751000.0000R72750.00
16606050510S1998/06/1086.884900.0000R-425712.00
16706050510S1998/07/1487.8324000.0000R-2107992.00
16806050510S1998/07/2095.7082970.0000R-7940229.00
16906050510S1998/07/2095.2550000.0000R-4762500.00
17006050510S1998/07/2095.2526668.0000R-2540127.00
17106050510S1998/07/2095.2533334.0000R-3175063.50
17206050510S1998/07/2295.502000.0000R-191000.00
17306050510S1998/07/2295.506904.0000R-659332.00
17406050510S1998/07/2295.5028000.0000R-2674000.00
17506050510S1998/07/2880.002870.0000R-229600.00
17606050510S1998/07/2880.0020000.0000R-1600000.00
17706050510P1998/07/2979.94500.0000R39970.00
17806050510S1998/07/2988.7132000.0000R-2838720.00
17906050510S1998/07/3090.507000.0000R-633500.00
18006050510P1998/08/1478.501000.0000R78500.00
18106050510P1998/08/1480.001000.0000R80000.00
18206050510P1998/09/0163.381000.0000R63380.00
18306050510P1998/09/0265.561000.0000R65560.00
18406050510P1998/10/1546.691000.0000R46690.00
18506050510P1998/10/2756.002000.0000R112000.00
18606050510P1998/12/0264.882000.0000R129760.00
18706050510P1998/12/2160.501000.0000R60500.00
18806050510P1999/02/1965.311000.0000R65310.00
18906050510S1999/03/1073.19422.0000R-30886.18
19006050510S1999/04/2073.14424339.0000R-31036154.46
19106050510S1999/04/2374.001600.0000R-118400.00
19206050510P1999/05/1868.63600.0000R41178.00
19306050510P1999/08/0961.062000.0000R122120.00
19406050510P1999/08/0961.13500.0000R30565.00
19506050510S1999/08/2764.3820000.0000R-1287600.00
19606050510S1999/10/1953.67424361.0000R-22775454.87
19706050510S1999/11/2363.6360.0000R-3817.80
19806050510P1999/12/1052.002000.0000R104000.00

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

Super User
Posts: 19,869

Re: Summing over certain time periods/intervals

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. 

Occasional Contributor
Posts: 19

Re: Summing over certain time periods/intervals

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.Smiley Happy

Solution
‎04-19-2013 01:26 PM
Super User
Posts: 19,869

Re: Summing over certain time periods/intervals

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.

Occasional Contributor
Posts: 19

Re: Summing over certain time periods/intervals

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?

Super User
Posts: 19,869

Re: Summing over certain time periods/intervals

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;

Occasional Contributor
Posts: 19

Re: Summing over certain time periods/intervals

Thanks a lot!! Smiley Happy

Occasional Contributor
Posts: 19

Re: Summing over certain time periods/intervals

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

Super User
Posts: 19,869

Re: Summing over certain time periods/intervals

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.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 434 views
  • 6 likes
  • 2 in conversation