BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
mexes
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

10 REPLIES 10
Reeza
Super User

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

mexes
Calcite | Level 5

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.

Reeza
Super User

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. 

mexes
Calcite | Level 5

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

Reeza
Super User

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.

mexes
Calcite | Level 5

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?

Reeza
Super User

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;

mexes
Calcite | Level 5

Thanks a lot!! Smiley Happy

mexes
Calcite | Level 5

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

Reeza
Super User

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.

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
  • 10 replies
  • 2091 views
  • 6 likes
  • 2 in conversation