Does one have advantage over the other? Do they give significantly different research outcomes? Sorry to you all for the back and forth; I'm new to SAS and research in general ... View more

When I use sql, I do not get " MERGE statement has more than one data set with repeats of BY values" . But will it work when one data set is annual and the other is daily ?   Thank you. ... View more

I have downloaded the daily security data with IID, PRCCD,CSHOC so I can calculate the daily market value of equity. However, the annual compustat data does not have iid. I'm interested in the market value of equity at the end of the fiscal year or the nearest. If I'm able to get these to match the annual compustat data, it should not present any serious research problem. No?    Any way, I tried using the datadata and SEDOL( The Stock Exchange Daily Official List(SEDOL) Code identifies issues) and got similar results as with gvkey.     ... View more

Thank you. No, current file does not have them but I'm downloading them now (will take a while; internet is slow). I will do as you have advised and get back. Thank you. ... View more

Thank you very much for your time and patience. I don't know what I would done without this platform.   No, it's not surprising that the daily data is more than the annual data. From the information I have not all the firms are actively trading on the stock exchange. Yes, I can merge the annual data before merging with the daily data. My question still is: take for instance that I want the market value as of the end of a particular year. If I still simply multiply share price by number of shares outstanding, I'm going to have different values since the daily stock prices have been matched against the annual shares outstanding. In this particular eg i want the share price at the end of the fiscal year times the closest annual number of shares outstanding (which should give me one value). I guess my question is : How do I need to do so that after matching annual and daily data sets I would get one value for an answer ( which should be the end of fiscal year value) and not multiple values, which should be the same nonetheless.   I count on your usual corporation ... View more

Thank you very much.I did run your codes and got this: NOTE: There were 86856448 observations read from the data set THEO.COPY_SECURITY. NOTE: There were 373633 observations read from the data set THEO.COPY_COMPUSTAT. NOTE: There were 86856448 observations read from the data set THEO.COPY_SECURITY. NOTE: There were 373633 observations read from the data set THEO.COPY_COMPUSTAT. NOTE: The data set WORK.WANT has 78137460 observations and 45 variables.   I noticed that the annual data has been matched against the daily data so that's good. But I have to merge the 'want' data set with other annual data sets. Will a simple proc sort by fyear help?   Thank you once again for your time and help !   ... View more

Thank you very much. Will try it. ... View more

Thank you. ... View more

Thank you very much.   I'm posting the log here; i got no error per se. Please ignore the data set definitions.    My next challenge is to calculate some variables at the end of the year fiscal year. My question is how to set the date right. Thank you.     13   proc sort data=theo.to_be_merged; 14       by gvkey; 15   run;   NOTE: Input data set is already sorted, no sorting done. NOTE: PROCEDURE SORT used (Total process time):       real time           0.00 seconds       cpu time            0.00 seconds     16   proc sort data=theo.security_daily; 17       by gvkey; 18   run;   NOTE: Input data set is already sorted, no sorting done. NOTE: PROCEDURE SORT used (Total process time):       real time           0.00 seconds       cpu time            0.01 seconds     19   data data3; 20       merge theo.to_be_merged(in=a) theo.security_daily(in=b); 21       by gvkey; 22       if b; 23   run;   NOTE: MERGE statement has more than one data set with repeats of BY values. NOTE: There were 2357018 observations read from the data set THEO.TO_BE_MERGED. NOTE: There were 86856448 observations read from the data set THEO.SECURITY_DAILY. NOTE: The data set WORK.DATA3 has 86857588 observations and 38 variables. NOTE: DATA statement used (Total process time):       real time           7:48.17       cpu time            53.68 seconds ... View more

As I indicated, I used the wrong identifier so I did check my data and did the right thing thanks to you guys.   On this issue, I have actually succeeded in merging them but I'm not sure if it is correct. I used these codes:   proc sort data=data1;     by gvkey; run; proc sort data=data2;     by gvkey; run; data data3;     merge data1(in=a) data2(in=b);     by gvkey;     if b; run;   Thanks ... View more

Thank you everybody for time and patience. I'm new to SAS and research in general and as has been pointed out to me, I think the mistake is coming from me. I'm sorry.   I think the correct identifier to use for merging my data is COMPANYID and not GVKEY. I have gone back and I'm compiling my data again. I'll tell you what happens.   Once again, thank you for your time and patience. I really appreciate it. ... View more

Thank you, sir for your reply.  Sir, dataset 1 and 2 are tenure and debt, respectively. I just used them as examples. As to the codes I used, as I said before, I searched online and saw a solution to a similar problem so I edited the codes and used it. I think the gvkey appearing as a single character in my codes reflect my limited knowledge of SAS and that's why I wanted to make sure I was doing the right thing.   Once again, thank you. ... View more

I'm so sorry,sir. I don't really get what you mean by my conversion data steps. So, I have provided the proc contents of the two variable here. Thank you.       SAS Output   The SAS System The CONTENTS Procedure   Data Set Name Observations Member Type Variables Engine Indexes Created Observation Length Last Modified Deleted Observations Protection Compressed Data Set Type Sorted Label   Data Representation   Encoding   THEO.DEBT 506856 DATA 22 V9 0 02/11/2017 05:31:48 424 02/11/2017 05:31:48 0   NO   NO     WINDOWS_64   wlatin1 Western (Windows)   Engine/Host Dependent Information Data Set Page Size Number of Data Set Pages First Data Page Max Obs per Page Obs in First Data Page Number of Data Set Repairs ExtendObsCounter Filename Release Created Host Created 65536 3292 1 154 146 0 YES C:\Users\toshiba-pc\Desktop\sas_stuff\debt.sas7bdat 9.0401M3 X64_8HOME Alphabetic List of Variables and Attributes # Variable Type Len Format Informat 1 2 3 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 4 Currency Char 11 $11. $11. GVKEY Num 8 BEST12. BEST32. USD Char 22 $22. $22. VAR5 Char 16 $16. $16. VAR6 Char 28 $28. $28. VAR7 Char 34 $34. $34. VAR8 Char 21 $21. $21. VAR9 Char 18 $18. $18. VAR10 Char 38 $38. $38. VAR11 Char 16 $16. $16. VAR12 Char 19 $19. $19. VAR13 Char 16 $16. $16. VAR14 Char 15 $15. $15. VAR15 Char 15 $15. $15. VAR16 Char 15 $15. $15. VAR17 Char 15 $15. $15. VAR18 Char 15 $15. $15. VAR19 Char 30 $30. $30. VAR20 Char 25 $25. $25. VAR21 Char 1 $1. $1. VAR22 Char 18 $18. $18. _ Char 22 $22. $22.   The SAS System The CONTENTS Procedure   Data Set Name Observations Member Type Variables Engine Indexes Created Observation Length Last Modified Deleted Observations Protection Compressed Data Set Type Sorted Label   Data Representation   Encoding   THEO.TENURE 2134412 DATA 13 V9 0 02/11/2017 04:48:53 96 02/11/2017 04:48:53 0   NO   YES     WINDOWS_64   wlatin1 Western (Windows)   Engine/Host Dependent Information Data Set Page Size Number of Data Set Pages First Data Page Max Obs per Page Obs in First Data Page Number of Data Set Repairs ExtendObsCounter Filename Release Created Host Created 65536 3135 1 681 651 0 YES C:\Users\toshiba-pc\Desktop\sas_stuff\tenure.sas7bdat 9.0401M3 X64_8HOME Alphabetic List of Variables and Attributes # Variable Type Len Format Informat Label 1 8 10 12 4 3 7 6 9 11 2 5 13 COMPANYID Num 8 11. 11. This is a unique indentifier denoting a company inside many tables of Capital IQ data feeds ENDYEAR Num 8 11. 11. End year of this record FSTYEAR Num 8       LnTenure Num 8       PERIODENDDATE Num 4 YYMMDDN8. DATETIME22.3 Period End Date PERSONID Num 8 11. 11. Unique identifier for a Person in the Capital IQ database PROFUNCTIONID Num 8 11. 11. This is the identifier of a given proFunction. A proFunction is a standardized way to describe titles because titles can be different across firms. PROID Num 8 11. 11. Unique identifier for a professional . A professional is the instance of a person being associated to a company (or educational institution, etc.) STARTYEAR Num 8 11. 11. Start year of this record TENURE Num 8       YEAR Num 4 11. 11. Fiscal Year YEARBORN Num 8 11. 11. Year the professional was born gvkey Char 1       Sort Information Sortedby Validated Character Set gvkey YES ANSI   ... View more

Thank you sir, for the reply. Below is the note I get from the log execution. I don't if this was what you meant. If meant I'm sorry; I started using SAS only recently. Thank you once again.   data theo.tenure; 19 set theo.tenure; 20 run; NOTE: There were 2134412 observations read from the data set THEO.TENURE. NOTE: The data set THEO.TENURE has 2134412 observations and 13 variables. NOTE: DATA statement used (Total process time): real time 4.63 seconds cpu time 0.71 seconds 21 data theo.debt; 22 set theo.debt; NOTE: There were 506856 observations read from the data set THEO.DEBT. NOTE: The data set THEO.DEBT has 506856 observations and 22 variables. NOTE: DATA statement used (Total process time): real time 6.95 seconds cpu time 0.61 seconds 23 proc sort data=theo.tenure; 24 by gvkey; 25 run; NOTE: There were 2134412 observations read from the data set THEO.TENURE. NOTE: The data set THEO.TENURE has 2134412 observations and 13 variables. NOTE: PROCEDURE SORT used (Total process time): real time 6.24 seconds cpu time 1.98 seconds 26 proc sort data=theo.debt; 27 by GVKEY; 28 run; NOTE: There were 506856 observations read from the data set THEO.DEBT. NOTE: The data set THEO.DEBT has 506856 observations and 22 variables. NOTE: PROCEDURE SORT used (Total process time): real time 7.49 seconds cpu time 1.18 seconds 29 data merged; 30 merge theo.tenure theo.debt; ERROR: Variable gvkey has been defined as both character and numeric. 31 by GVKEY; 32 run;     ... View more