About EJAA

EJAA · ‎11-03-2017

Hi ChrisNZ, I followed your codes and directions closely and glad to notify you that it did work perfectly. The problem is now resolved and it worked really faster.Thanks very much ChrisNZ and all who helped solve the query. regards ejaa.

EJAA · ‎11-02-2017

Hi ChrisNZ I sincerely apologies for the late update. I had few issues yesterday and was off hence me not updating you with reference to the codes you sent. Thanks very much for the understanding and really appreciate the great efforts. Please just as you opinionated earlier on, we are getting there. I have run the code and it worked perfectly on the sample above and is very fast of which i tried to follow the codes so i can modify the variables to enable me apply the codes to the bigger data i have but didnt work. Please as i could only indicate the nature of the data when posting the question, I couldnt show much data but in my sample data HELP, the column runs from d1-d313 with most have value (companyid). Thus a specific firm could be linked to more than one firm. For instance a specific firm could be linked to just one while others two while others 20 etc. So firm with id 10001 is linked to more than two firms in the actual but couldnt show all as you requested i only post few sample for assessment and direction.Hence i have something like this below for data help CompanyID lpermno d1 d2 d3 d4 d5 d6 d7 d8 d9 d10 10675 10001 8433 1723398 2070824 18195 620983 10002 576969 8433 959 10025 576969 1723398 9137 10028 2070824 18195 8433 12540 576969 Also please in the data help1, aside this variables, i have other variables price, market_returns in addition to CompanyID lpermno date volume stk_returns. I have tried several times trying to follow your codes but still getting it wrong. Please we are almost there and i seek for further direction to enable me apply on the main data as the codes work on the small sample data posted earlier. Thanks very much ChrisNZ for this great assistance.regards ejaa.

EJAA · ‎10-31-2017

Thanks ChrisNZ for the great efforts. Please when we look into dataset "help" we see that firm 10001 is having firms with in colum d1 and d2 with companyid 8433 & 1723398. Thus these two firms are connected to firm 10001. On the beta volume sorry abt that, it should be as shown below. Thus the average daily volume of the two firms connected to firm 10001. Thanks once again.regards Final Table for firm with id 10001. CompanyID lpermno date volume stk_returns beta_volume (obtained from firms connected to firm 10001) 10675 10001 01/03/2000 142 0.40 400 (thus 300+500/2) 10675 10001 01/04/2000 245 0.45 480 10675 10001 01/05/2000 285 0.42 430 10675 10001 01/07/2000 181 0.43 490 10675 10001 01/10/2000 149 0.44 420

EJAA · ‎10-31-2017

Please the beta_volume is the daily average/mean of the firms connected to a specific firm. So for instance in the example given, firm 10001 is connected to two firms thus 8433 & 1723398. Hence i obtain the average of daily volume of these two firms and link it to firm 10001 by date to produce the final table to run the regression. In my earlier code, this what i used to obtain the mean of firms connected to a specific firm. %macro rpt5; %Do R = &C1 %to &C2; proc summary data = ase.sum&R. ; by date; var vol; output out=ase.sum_&R. mean=beta_vol ; run; %end; %mend rpt5 ; %rpt5 ; Thanks very much.

EJAA · ‎10-30-2017

Hi ChrisNZ. Thanks once again. Following your direction, this is how my dataset looks like: Data set “help” contains the number of firms connected to each firm.d1-d3 are companyid’s of firms connected to for instance firm with lpermno 10001. CompanyID lpermno d1 d2 10675 10001 8433 1723398 620983 10002 576969 8433 959 10025 576969 1723398 9137 10028 2070824 18195 Data set “help1” contains daily volume and skt_returns for 9700 firms. CompanyID lpermno date volume stk_returns 10675 10001 01/03/2000 142 0.40 10675 10001 01/04/2000 245 0.45 10675 10001 01/05/2000 285 0.42 10675 10001 01/07/2000 181 0.43 10675 10001 01/10/2000 149 0.44 620983 10002 01/03/2000 200 0.30 620983 10002 01/04/2000 280 0.35 620983 10002 01/05/2000 230 0.32 620983 10002 01/07/2000 190 0.33 620983 10002 01/10/2000 240 0.34 8433 10003 01/03/2000 300 0.10 8433 10003 01/04/2000 380 0.15 8433 10003 01/05/2000 330 0.12 8433 10003 01/07/2000 390 0.13 8433 10003 01/10/2000 340 0.14 576969 10004 01/03/2000 400 0.80 576969 10004 01/04/2000 480 0.85 576969 10004 01/05/2000 430 0.82 576969 10004 01/07/2000 490 0.83 576969 10004 01/10/2000 440 0.84 1723398 10000 01/03/2000 500 0.60 1723398 10000 01/04/2000 580 0.65 1723398 10000 01/05/2000 530 0.62 1723398 10000 01/07/2000 590 0.63 1723398 10000 01/10/2000 500 0.64 Final Table for firm with id 10001. CompanyID lpermno date volume stk_returns beta_volume (obtained from firms connected to firm 10001) 10675 10001 01/03/2000 142 0.40 21 10675 10001 01/04/2000 245 0.45 30 10675 10001 01/05/2000 285 0.42 40 10675 10001 01/07/2000 181 0.43 84 10675 10001 01/10/2000 149 0.44 41 From data set help, I first create a portfolio of firms connected to each firm from data set help1 to obtain portfolio of their volume called beta_volume (mean of volume for each day). I then created dataset for each firm obtain the "final table" shown above. In the end, I run a regression where I regress volume on beta volume and stk_returns for each firm. This the work i used the earlier codes i posted to do.Thanks very much. regards ejaa.

EJAA · ‎10-30-2017

Thanks ChrisNZ. Attached is a small sample of my dataset with reference to the nature of the data. In the end i want to obtain regression output for each firm. Thanks.

EJAA · ‎10-30-2017

Hi ChrisNZ Thanks for the direction. Please attached sample data.Thanks in advance for the help. regards

EJAA · ‎10-30-2017

The number of rows for the data set with 9700 firms is 19725636 while the other is having 7927 rows. Thanks.

EJAA · ‎10-28-2017

Hi All, I'm running the codes below on two data sets but its taking extremely long to process. I don't really know what is wrong with my codes that is taking too long to process when i run. I have about 9700 firms in one data set and in another data set a list of firms connected to each of the 9700 firms. Due to the slowness i did run from 1-50 firms but that is even not processing after a day. The code gives no errors. Any advice for me to correct the slowness. Thanks in advance for the help. Regards ejaa. /*****getiing the count of number of lpermno in help*****/ proc sql noprint; select count(lpermno) into: A from ase.help ; quit ; %put &A ; /********getting a sequence order to observations**********/ Data ase.help ; Set ase.help; Retain seq 0 ; Seq = seq + 1 ; run ; /*******Transpose help to get long format*********************/ proc transpose data=ase.help out=ase.help_trans; by lpermno seq; run; Data ase.help_trans (Rename = (COL1 = companyid)) ; Set ase.help_trans ; Where COL1 ne . ; Run ; %let C1 = 1 ; %let c2 = 50 ; /***********Separating each set of firms attched to lpermno*************/ %macro rpt; %Do R = &C1 %to &C2; data ase.lp&R. (rename = (Seq = seq&R.)) ; Set ase.help_trans ; Where seq = &R ; Run ; proc sort data = ase.lp&R.; by companyid ; run ; %end ; %mend rpt ; %rpt ; /***********Merge separated sets to help1******************/ Proc sort data = ase.help1 ; By companyid ; run ; %macro rpt1; %Do R = &C1 %to &C2; data ase.com_1 ; Merge ase.lp1 - ase.lp&R. ; By companyid ; run ; %end ; %mend rpt1 ; %rpt1 ; Proc sort data = ase.com_1 ; By companyid ; run ; data ase.com ; Merge ase.help1(in= a drop = lpermno ) ase.com_1 ; By companyid ; If a ; run ; /**************Select different lpermno from help********************/ %macro rpt2; %Do R = &C1 %to &C2; Proc sort data = ase.lp&R. nodupkey out = ase.f&R.; By lpermno ; run ; %end ; %mend rpt2 ; %rpt2 ; /***************************merge lpermno from help to help 1 *****************************/ Proc sort data = ase.help1 ; By lpermno ; run ; %macro rpt3; %Do R = &C1 %to &C2; Data ase.Com_long1 ; merge ase.f1 - ase.f&R. ; by lpermno ; Run ; %end ; %mend rpt3 ; %rpt3 ; Data ase.Com_long ; merge ase.help1 (in = a) ase.Com_long1 ; by lpermno ; If a ; Run ; /*************Getting different sets of firms out of help1***********************/ %macro rpt4; %Do R = &C1 %to &C2; data ase.sum&R. ; Set ase.com ; where seq&R. = &R ; Run ; Proc sort data = ase.sum&R. ; By date ; run ; %end ; %mend rpt4 ; %rpt4 ; /*******getting mean prices by date in all the sets*************************/ %macro rpt5; %Do R = &C1 %to &C2; proc summary data = ase.sum&R. ; by date; var vol; output out=ase.sum_&R. mean=beta_vol ; run; %end; %mend rpt5 ; %rpt5 ; /********************separating lpermno to different sets from help1*********************************/ %macro rpt6; %Do R = &C1 %to &C2; data ase.price&R. ; Set ase.Com_long ; where seq&R. = &R ; Run ; %end ; %mend rpt6 ; %rpt6 ; /***********************Merging matching lpermno and attched firms by date*********************************/ %macro rpt7 ; %Do R = &C1 %to &C2; data ase.final&R.(drop = _TYPE_ _FREQ_ _NAME_ ) ; Merge ase.price&R. (in = a) ase.sum_&R. (in = b) ; By date ; If a and b ; run ; %end ; %mend rpt7 ; %rpt7 ; /************************Getting regression of each set**********************************/ %macro rpt8; %Do R = &C1 %to &C2; proc reg data=ase.final&R. outest = ase.result&R. ; By lpermno ; model vol = beta_vol stk ; run; %end ; %mend rpt8 ; %rpt8 ; /**********getting results together****************************/ %macro rpt9; %Do R = &C1 %to &C2; Data ase.reg1 ; Set ase.result1 - ase.result&R. ; run; %end ; %mend rpt9 ; %rpt9 ; /**********************END********************************/

EJAA · ‎10-17-2017

Hi RW9 Thanks for the direction in posting questions. Will do that next time. Rgds. ejaa.

EJAA · ‎10-17-2017

Hi error-prone Thanks very much. I followed your codes and it worked perfectly. really appreciate the assistance. rgds ejaa

EJAA · ‎10-17-2017

Yes i want to move values to the left to replace zero when values are available just as you have said. For instance some firms have values in column 900 and want to move it to the first column. any help for me? Thanks

EJAA · ‎10-17-2017

Hi, I have a large number of columns (1000s) and rows as shown in table 1 with some cells having no observation. I want to create a new table where only cells with values will remain starting from column (fdi1) as shown in table 2 for easy cross checking etc. I tried the following but it didn't seem to work as a newbie. how do i resolve this problem. thanks in advance for the help. rgds. data want; set have; by ID; if d1-d8 ^=.0 then keep; else remove; run; TABLE 1 ID d1 d2 d3 d4 d5 d6 d7 d8 1 8433 1723398 18195 18326 4753 1451 10494 1190 2 0 0 0 0 0 0 4554 0 3 0 0 0 0 0 0 0 5822 4 2070824 12153 16120 8529 638735 0 0 0 5 30349 25629 32588 1539 19175 17913 2621 2061996 6 0 0 0 18973 0 0 0 0 7 22693 602643 27312 30456 23711 31893 6764 29269 8 5257 1616257 32504 10841 731286 1937 33008 20757 9 0 5841 0 0 0 0 0 0 10 0 0 0 0 0 0 6001 0 11 33380 253576 12134 23923 33262 14338 25699 21758 12 461440 1906253 10896 13034 18385 15501 2486 91382 13 467544 275 33323 20081 71572 14365 26759 26178 14 272327 7453 6339 7636 0 0 0 0 15 0 0 0 0 56684 0 258 0 table 2 ID d1 d2 d3 d4 d5 d6 d7 d8 1 8433 1723398 18195 18326 4753 1451 10494 1190 2 4554 3 5822 4 2070824 12153 16120 8529 638735 5 30349 25629 32588 1539 19175 17913 2621 2061996 6 18973 7 22693 602643 27312 30456 23711 31893 6764 29269 8 5257 1616257 32504 10841 731286 1937 33008 20757 9 5841 10 6001 11 33380 253576 12134 23923 33262 14338 25699 21758 12 461440 1906253 10896 13034 18385 15501 2486 91382 13 467544 275 33323 20081 71572 14365 26759 26178 14 272327 7453 6339 7636 15 56684 258

EJAA · ‎10-16-2017

I have the connections stored in another file. I want to read from the two data sets to create the groups/connected firms. any directions for me. thanks in advance.rgds

EJAA · ‎09-27-2017

Hi Fredrike With reference to the connections, yes i do have them on a different sheet. Thus each firm and the firms connected to it. Thanks

Online Status	Offline
Date Last Visited	‎11-13-2019 10:44 PM

Re: Pair Regression Estimation in SAS

Pair Regression Estimation in SAS

Re: Matching Company Names

Re: Matching Company Names

Re: Matching Company Names

Re: Matching Company Names

Re: Matching Company Names

Re: Matching Company Names

Re: Using Propensity Score Matching to select similar firms

Using Propensity Score Matching to select similar firms

Re: Code running slow in SAS

Re: Code running slow in SAS

Re: Amihud (2002) Liquidity Measure

Re: Matching Company Names

Re: Code running slow in SAS

Re: Code running slow in SAS

Re: Code running slow in SAS

Re: Code running slow in SAS

Re: Code running slow in SAS

Re: Code running slow in SAS

Re: Code running slow in SAS

Re: Code running slow in SAS

Code running slow in SAS

Re: keeping cells with values

Re: keeping cells with values

Re: keeping cells with values

keeping cells with values

Re: Forming Portfolio of Connected firms

Re: Forming Portfolio of Connected firms