About Laoban_James

Laoban_James · ‎03-14-2016

How to insert a row/observation within a table? And by the meantime, I also filled the whole table with observations for the whole time span. Say, in the example, for each ID, I want to creat the state for every time (1 2 3 4 5 6 7). If there is a new state, just follow it. If there is no new state, follow the last state observed. I have searched some codes, but they all add new rows to the end of the table. Thanks you for your help in advance. data have; input ID $ time $ state; cards; a 1 23 a 5 25 b 1 65 b 2 33 b 7 28 c 2 13 ; run; So the data output I get in mind is that: a 1 23 a 2 23 a 3 23 a 4 23 a 5 25 a 6 25 a 7 25 b 1 65 b 2 33 b 3 33 b 4 33 b 5 33 b 6 33 b 7 28 c 1 .(no state observed yet) c 2 13 c 3 13 c 4 13 c 5 13 c 6 13 c 7 13

Laoban_James · ‎01-28-2016

Actually, this question originates from this: https://communities.sas.com/t5/SAS-Procedures/How-to-run-a-rolling-regression-with-two-datasets/m-p/246512/highlight/false#M56241 Really desparately crasy...

Laoban_James · ‎01-28-2016

Thank you, Keshan. But the problem here is that the data is so big, so creating this 'want' data set is too costly. What I get in mind is that, is it possible to everytime use one observation from A and then according to it, find proper data from B , and feed regression results back to A. sth like this : https://communities.sas.com/t5/Base-SAS-Programming/Can-I-call-execute-macro-within-a-DO-loop/m-p/246584#M46154

Laoban_James · ‎01-27-2016

Can I call/execute macro within a DO loop? something like data TWO; set ONE; do until(last.ID) %macro( some variable in TWO, ONE, maybe another dataset ) end; run; is this possible? thx a lot.

Laoban_James · ‎01-27-2016

Say with data set A. DATE ID 10 1 10 2 10 3 20 1 20 2 20 3 30 1 30 2 30 3 And data set B, DATE ID x y 1 1 0.070933926 0.323055974 2 1 0.406570761 0.13760953 3 1 0.883535963 0.578733088 4 1 0.016231788 0.729134018 5 1 0.668424989 0.761197387 6 1 0.242259712 0.810040071 7 1 0.213543345 0.058647549 8 1 0.857330638 0.531532208 9 1 0.501064997 0.743060726 10 1 0.018354547 0.072328341 11 1 0.99627551 0.140234082 12 1 0.267813864 0.134323054 13 1 0.132534742 0.716864341 14 1 0.107630158 0.498794317 15 1 0.814832803 0.678847109 16 1 0.194916825 0.652946461 17 1 0.24655657 0.348632146 18 1 0.581696172 0.541978606 19 1 0.448507082 0.723949992 20 1 0.725078176 0.336086311 21 1 0.572441245 0.70316834 22 1 0.638282508 0.529384198 23 1 0.967807781 0.159573909 24 1 0.962864907 0.683835901 25 1 0.772681849 0.53964572 26 1 0.67532915 0.484330887 27 1 0.946038446 0.569370775 28 1 0.734410321 0.801076677 29 1 0.018660865 0.943685516 30 1 0.102126634 0.12693274 1 2 0.337826696 0.381279823 2 2 0.402967304 0.784483754 3 2 0.528027957 0.132032813 4 2 0.679449095 0.706947401 5 2 0.083379513 0.431491854 6 2 0.086233166 0.237205938 7 2 0.247418031 0.816204954 8 2 0.877926644 0.738031694 9 2 0.234003429 0.900287427 10 2 0.878105682 0.430906847 11 2 0.159710129 0.647456551 12 2 0.245602139 0.007026074 13 2 0.596617096 0.49183371 14 2 0.858081967 0.998086222 15 2 0.946591278 0.508973381 16 2 0.030718654 0.09659358 17 2 0.383475095 0.001080811 18 2 0.47175129 0.88797923 19 2 0.231755643 0.706321812 20 2 0.26818677 0.54529103 21 2 0.605350394 0.891914056 22 2 0.278178314 0.14754076 23 2 0.179958498 0.622006208 24 2 0.536177448 0.921842128 25 2 0.881522327 0.772927816 26 2 0.034479121 0.836198033 27 2 0.61827926 0.119682909 28 2 0.363028818 0.536296862 29 2 0.416057939 0.209719423 30 2 0.319097646 0.971274071 7 3 0.540217872 0.445921819 8 3 0.086111398 0.304460289 9 3 0.193343887 0.452840456 10 3 0.075337725 0.387945463 Baseically, I want to write a loop, what read one row of data A, and then find the 10 observations from data B with A.date-10<B.date<=A.date and B.ID=A.ID, and then get the regression coefficient with this regression and then put it back to data set A. I have found some codes which can achieve this , but they are too costly, since they just collapse two data sets into one and then reg. However, my dataset is so big that it is impossible to do like that. I am wondering if it will be possible to write a loop that go through all observations in data A, and for each observation of data A, find proper data from data B, and get regression results and put it back to data A, or just create a new data to store these results first. Could someone help a rookie like me to write a program like this? 🙂 many kisses...

Laoban_James · ‎07-01-2015

Keshan, Thanks so much! I will definitely buy you a dinner when coming back to Beijing. Another question followed up is that the set_2 is not always there. Say, I get set_1 data '1990 7 AAA 22', but since there is no holdings at 1990 6 for AAA, so there is no corresponding point in set_2. In this case, I would like to assign 0 to set_1. I am thinking about use UPDATE to do this, but I cannot quite realize it. Best, James

Laoban_James · ‎06-27-2015

And another problem is that,,, not every stock (denoted by TICKER) or at every time point , has a holding data. So if it is none , I would like to assign 0 to its holding. This is really complicated for a newbie like me. Thanks you guys for helping me.

Laoban_James · ‎06-27-2015

Hey, SASers I hope you can help me on this issue. I do not know how to merge data in this kind of situation. ------------- data set 1 year month ticker price 1990 1 AAA 22 1990 2 AAA 23 1990 3 AAA 21 1990 4 AAA 28 data set 2 year month ticker holding 1989 12 AAA 77 1990 3 AAA 53 ----------------- I want to create merge data set 2 into data set 1. However, month 1-3 in data set 1 will take the value of the month 12 of last year in data set 2, and month 4-6 of month 3 this year, and month 7-9 of month 6 this year, and month 10-12 of month 9 this year. So the output in my mind is: year month ticker price holding 1990 1 AAA 22 77 1990 2 AAA 23 77 1990 3 AAA 21 77 1990 4 AAA 28 53 Please ask me if I am not clear enough. Thanks in advance, Cheers, James

Laoban_James · ‎06-23-2015

Thank you all. I somehow get some idea how to do that. But still I cannot modify your answers to my final goal. My data set is like: year month stock fund share 1988 3 A alpha 10 1988 3 A beta 33 1988 3 A gamma 12 1988 3 B alpha 28 1988 3 B gamma 90 1988 3 C ita 13 1988 6 A alpha 60 1988 6 A beta 53 1988 6 A gamma 13 1988 6 B alpha 86 1988 6 B gamma 90 1988 3 C ita 13 so my final output desired is like: year month stock first-fund first-fund-share second-fund second-fund-share first-two-fund-share 1988 3 A beta 33 gamma 12 45 ..... ..... ..... I used the following code: proc means data=raw noprint; class stock year month; types () stock year month; var SHARES; output out=top10list(rename=(_freq_=Numberfunds))sum= mean= idgroup( max(SHARES) out[10] (SHARES FUNDNO stock year month)=)/autolabel autoname; run; But it does not give me the fund holding for different years and months separately. Unhappy...-^-

Laoban_James · ‎06-22-2015

updated already. Thx in advance.

Laoban_James · ‎06-22-2015

updated already. Thx in advance.

Laoban_James · ‎06-22-2015

Hey, Guys, I am really new to SAS, and trying to learn. But now I got stuck with this problem. Hopefully you can help me out of it. For each stock, I get holding shares from different funds (holding funds numbers may vary). Now I have summed all the holdings of all funds, but now I want to calculate the holding shares of top one/two/three/whatever funds in terms of the shares they hold. Any idea how to realize this? Thx in advance. Laoban Thanks, guys, the data is quite big, so I will just post some man-made data. I think this will be enough. Stock Fund Share A alpha 60 A beta 33 A gamma 12 B alpha 88 B gamma 90 C ita 13 So sorry for my poor English, so basically what I want is. I want to get the holding shares of the top one fund holding for a specific stock: A - 60, B - 90, C - 13. or the top two funds' holding: A - 93, B - 178, C - 13 or NA for this case. ---------------------------------------------------------------- Thank you all. I somehow get some idea how to do that. But still I cannot modify your answers to my final goal. My data set is like: year month stock fund share 1988 3 A alpha 10 1988 3 A beta 33 1988 3 A gamma 12 1988 3 B alpha 28 1988 3 B gamma 90 1988 3 C ita 13 1988 6 A alpha 60 1988 6 A beta 53 1988 6 A gamma 13 1988 6 B alpha 86 1988 6 B gamma 90 1988 3 C ita 13 so my final output desired is like: year month stock first-fund first-fund-share second-fund second-fund-share first-two-fund-share 1988 3 A beta 33 gamma 12 45 ..... ..... ..... I used the following code: proc means data=raw noprint; class stock year month; types () stock year month; var SHARES; output out=top10list(rename=(_freq_=Numberfunds))sum= mean= idgroup( max(SHARES) out[10] (SHARES FUNDNO stock year month)=)/autolabel autoname; run; But it does not give me the fund holding for different years and months separately. Unhappy...-^-

Online Status	Offline
Date Last Visited	‎03-14-2016 02:44 PM

How to insert a row/observation within a table?

Re: Can I call/execute macro within a DO loop?

Re: How to run a rolling regression with two datasets?

Can I call/execute macro within a DO loop?

How to run a rolling regression with two datasets?

Re: How to merge two data sets with time difference?

Re: How to merge two data sets with time difference?

How to merge two data sets with time difference?

Re: How to select and keep the highest variable?

Re: How to select and keep the highest variable?

Re: How to insert a row/observation within a table?

Re: How to insert a row/observation within a table?

Re: How to merge two data sets with time difference?

Re: How to merge two data sets with time difference?

Re: How to select and keep the highest variable?

Re: How to select and keep the highest variable?

How to insert a row/observation within a table?

Re: Can I call/execute macro within a DO loop?

Re: How to run a rolling regression with two datasets?

Can I call/execute macro within a DO loop?

How to run a rolling regression with two datasets?

Re: How to merge two data sets with time difference?

Re: How to merge two data sets with time difference?

How to merge two data sets with time difference?

Re: How to select and keep the highest variable?

Re: How to select and keep the highest variable?

Re: How to select and keep the highest variable?

How to select and keep the highest variable?