Dear Sas members, I have two stocks per portfolio which I randomly selected.Each stock stays three days in the portfolio and have returns per day. Then the stocks are sold and are invested in the new portfolio. So I have trouble in porgramming the following; 1) for every portfolio I have to calculate the cumulative return per stock (ok, works for me) 2) At the end of the portfolio (day 3) the stocks are sold and I have to sum the cumulative returns, divide it by two and reinvest it equally in the next two stocks of portfolio 2, and so on. I have difficulties implementing step 2... I have uploaded sample data in the attachement; this is my code for step 1 proc sort data=WORK.QUERY_FOR_RETURNS out =RENDER; by PORT STOCK DATE; run ; 　 data POD_100; set RENDER; by PORT STOCK; 　 　 *Calculating the retunr per stock in the portfolio; if first.stock then do ; if ret=. then cumret=1 ; end ; else if ret NE . then do ; cumret=cumret*ret+cumret; end ; retain cumret; 　 　 　 run ; ... View more

Dear Sas users, I have difficulties filtering a dataset; I only want these observations that have no missing values between day 840 and 845. For example, STOCK DATE A 840 A 841 A 842 A 843 A 844 A 845 B 840 B 841 B 842 C 840 C 841 C 842 C 843 C 844 C 845 D 842 D 843 D 844 D 845 I can only take these stocks that have non missing values between 840 and 845. So I Cannot select stock C and B. Ans I want a new dataset only with the variables A and D. How do I use a proper filter technique? Kind regards, Stefaan Message was edited by: OzoneX15 Message was edited by: OzoneX15 ... View more

Hi all, I have a dataset with 5 observations; OBSERVATION F T J D D I want to spread the observations like this (so the first obervation has to be there for the first five days, the second observations has to be there for the next five days, (from day 6 to 10,... and so on); I want to come up with the following dataset; DAY,OBSERVATION 1,F 2,F 3,F 4,F 5,F 6,T 7,T 8,T 9,T 10,T 11,J 12,J 13,J 14,J 15,J 16,D 17,D 18,D 19,D 20,D 21,D 22,D 23,D 24,D 25,D I have programmed this in the following way, options nomprint nosymbolgen nomlogic; %macro result; data %do i=1 %to 5; Data VECTOR_PRE (drop=obsnum); obsnum=&i; set VECTOR_ point=obsnum; output; stop; run; DATA OUTPUT&i; SET VECTOR_PRE; day=0; do day=&i*(30)-30+1 to &i*(30); output; end; run; %end; data sasuser.want; set output: indsname=dsn; drop dsn; run; %mend ; %result Is there a way to do this in a datastep, so without using a macro? Kind regards, Stefaan Message was edited by: OzoneX15 ... View more

Dear Sas members, I have difficulties in selecting a random sample. I have to create 1000 portfolios. These portfolios have to pick random stocks with weights (ranging from 0 to 0.05) UNTIL the sum of 1 is reached. Which has to change every time a new portoflio is created. -->So to be clear: I have 1 portfolio that has to select a stocks with random weights until the weights sum up to 1 or close to one. Than again I assign random weights to the stocks and create a second portfolio that randomly chooses stocks until the sums of the weights is 1;.. and so on... The only column I have are the STOCKS (500 stocks-500 rows) STOCKS AN8068571086 ANN4327C1220 ANN6748L1027 AT0000603709 AT0000609607 AT0000620158 AT0000633300 AT0000644505 AT0000652011 AT0000676903 AT0000720008 AT0000729108 AT0000737705 AT0000741053 AT0000743059 AT0000746409 AT0000809058 AT0000821103 I have come up with this code but I am stuck /*create random weights between 0 and 5 for each stock.*/ data Sample1; set SASUSER.RANDOM_WEIGHTS; rand = 1+(5-1)*ranuni(692); if rand ge 0 and rand le 5; run; /*selecting a random sample from a data set is to, first, use a DATA step to generate a random vector, then use PROC sort to rearrange the data by that random vector and then select first k observations*/ DATA Sample2 ; SET Sample1 ; random=RANUNI(-1); /* GENERATE A RANDOM VECTOR */ run; PROC SORT DATA=Sample2; BY random; /* SORT OBSERVATIONS BY THE RANDOM VECTOR */ run; Thanks for helping me out if you know the solution... Kind regards, Stefaan Message was edited by: OzoneX15 ... View more

Dear Sas users, I have a difficult problem which I cannot solve.... I think to do it with an advanced merge but I still do not succeed. I have a database with transaction data. For example, here a mutual fund is trading in one stock (Isin code= BE0003565737). This is my input data in SAS. So this fund is trading (selling or buying this stock) on 19/02/2004, 19/03/2004, 24/03/2004,... and so on. But I want to know exactly how much the return is. So for example the first buy is on 19/02/2004 but the sell is on 11/08/2003. the problem is not the merging itself but the stocks it sells or buys. Because this fund is buying 3000 stocks and selling 3800 stocks.... I have to keep track the number of stocks when I do a merge (for this example, I sells 3000 stocks but it holds 800 stocks and this 800 stocks I have to merge with the following buy date... I want to come up with this table in SAS where all transactions are exactly mathcing (first in/first out). I hope I made myself clear... thanks in advance You can have a look at the exel.. perhaps more clear... http://users.telenet.be/webdesignsite/Problem_Stefaan_Pauwels.zip I have tried to create two tables (exactly copies of the table beneath), so exactly two tables and to join them but still don't know how to take into account the number of stocks) tdate fund ISIN Price transmod NumberStocks 19/02/2004 100174701 BE0003565737 47 buy 3000 19/03/2004 100174701 BE0003565737 44,8 buy 3800 24/03/2004 100174701 BE0003565737 44,59 buy 12050 23/06/2005 100174701 BE0003565737 66,65 buy 3900 11/11/2005 100174701 BE0003565737 73,15 buy 3000 14/11/2005 100174701 BE0003565737 73,2 buy 3800 17/05/2006 100174701 BE0003565737 84,3 buy 250 12/06/2006 100174701 BE0003565737 78,05 buy 800 11/08/2003 100174701 BE0003565737 87,85 sell 40000 11/08/2006 100174701 BE0003565737 87,85 sell 3800 21/09/2006 100174701 BE0003565737 84,75 buy 530 23/11/2006 100174701 BE0003565737 85,3 buy 30 4/12/2006 100174701 BE0003565737 85,95 buy 20 23/01/2007 100174701 BE0003565737 97,65 buy 260 13/02/2007 100174701 BE0003565737 97,8 sell 400 23/02/2007 100174701 BE0003565737 96,2 buy 150 6/03/2007 100174701 BE0003565737 89,67 sell 300 6/03/2007 100174701 BE0003565737 89,67 sell 40000 This is the final output table I want to come up with... tdate fund ISIN Price transmod NumberStocks tdate_sell fund1 ISIN1 Price1 transmod1 NumberStocks1 19/02/2004 100174701 BE0003565737 47 buy 3000 11/08/2006 100174701 BE0003565737 87,85 sell 3000 19/03/2004 100174701 BE0003565737 44,8 buy 3800 11/08/2006 100174701 BE0003565737 87,85 sell 800 19/03/2004 100174701 BE0003565737 44,8 buy 3000 13/02/2007 100174701 BE0003565737 97,8 sell 400 19/03/2004 100174701 BE0003565737 44,8 buy 2600 6/03/2007 100174701 BE0003565737 89,67 sell 300 19/03/2004 100174701 BE0003565737 44,8 buy 2300 6/03/2007 100174701 BE0003565737 89,67 sell 2300 24/03/2004 100174701 BE0003565737 44,59 buy 12050 6/03/2007 100174701 BE0003565737 89,67 sell 37700 23/06/2005 100174701 BE0003565737 66,65 buy 3900 6/03/2007 100174701 BE0003565737 89,67 sell 33800 Thanks in advance, Stefaan Message was edited by: OzoneX15 Message was edited by: OzoneX15 ... View more