Hi, Bob.

Okay, I sort of see what you mean, but I don't have the entire picture in view, so here is what I suggest. Put together a small file of exactly the incoming data layout for two or three stocks (and cash, if there is any incoming cash data). Then give a blow by blow account of the rules to compute the output, and what the output should be. I know that's a lot of work, but once I see all that, I'll be able to show you how to do it in SAS, possibly in a small number of steps.

I recommend that you send all this to the SAS-L list, too. So far you've given the pieces in kind of disconnected fashion, and so there's no guarantee the answers to the pieces will fit together. Once they see exactly what you want, they'll be able to give it to you. And you're likely to get an even "better" solution than what I give you.

Here's the blow by blow:

After you read everything in:

Step 1. calculate cost market value of each security you purchased roll forward to next month.

Step 2: adjust starting shares for any splits. Calculate the dollar dividends you receive for each security.

Step 3. buy and sell trades for month as indicated and update ending share values along with market value and cost value for each stock

Step 4. continue in this loop till you've gone through all the days.

Step 5. summary the data for purchases, sales and dividends. So in addition to having these numbers for each ticker when we sum we have these values for the portfolio for each date

Step 6 merge this data back into the ticker data

Step 7 recalculate the starting and ending values for cash

Step 8 summary the portfolio. You're done.

This was not the logic I wanted to use. I kept saying that I should not have to merge the summary data back into the dataset. It was wasteful and inefficient. No one would suggest any other way to handle the problem. The q2 spreadsheet matches the sas program and is a correct reflection of what's going on. The data tabs and trades tab are the data and the port1 tab is basically the sas code in excel.

I would like to make the initial cash position more explicit and I think that perhaps the right way here is to exclude cash from the initial data set but I'm not sure. Please see my post: the right way to write sas code by rss.

(