About joon1

joon1 · ‎02-26-2024

Thank you so much for your help, PaigeMiller. Have a great week. Joon1

joon1 · ‎02-25-2024

Thanks a lot for your prompt reply. I meant for gvkey1 # 1011, the average score = score sum of other gvkeys (gvkey # 1013, 1034,1036,1045,1065,1072,1073)/ 24 (the number of all other observations, not 7) Thank you for your help Sincerely, Joon1

joon1 · ‎02-25-2024

Dear Madam/Sir, I would like to calculate the score average of other firms in the same year. For instance, for gvkey1 # 1011, the average score = score sum (gvkey # 1013, 1034,1036,1045,1065,1072,1073)/7. Any programming help would be greatly appreciated. gvkey1 year score 1011 1988 0.1508 1011 1988 0.0851 1011 1988 0.0258 1011 1988 0.0097 1013 1988 0.0215 1013 1988 0.0154 1013 1988 0.0842 1013 1988 0.0006 1034 1988 0.0434 1034 1988 0.0193 1034 1988 0.0324 1036 1988 0.004 1036 1988 0.0393 1036 1988 0.0177 1036 1988 0.0103 1045 1988 0.0712 1045 1988 0.0059 1045 1988 0.0636 1045 1988 0.007 1045 1988 0.0619 1065 1988 0.0144 1065 1988 0.0201 1065 1988 0.0006 1065 1988 0.0101 1072 1988 0.0663 1073 1988 0.0021 1073 1988 0.0462 1073 1988 0.006 Thank you Joon1

joon1 · ‎01-31-2024

It works! Thank you so much, FreelanceReinh.

joon1 · ‎01-29-2024

Thanks for your quick response. I have inserted 'id' in the code. However, the result is the same as before. ID is not assigned properly and last.id is not working. I want to remain the last observation (red colored). Any help will be highly appreciated. proc sort data=m2b; by ticker prd_yr anndats; run; proc freq data=m2b; tables ticker*prd_yr*anndats/noprint out=m2c; data m2c; set m2c; id=_n_; run; proc sort data=m2c; by ticker prd_yr anndats id; run; data m2d; merge m2b m2c; by ticker prd_yr anndats; proc sort data=m2d; by ticker prd_yr anndats id; run; data m3; set m2d; by ticker prd_yr anndats id; if last.id; run; Obs ticker prd_yr prd_mon anndats id measure pdicity val_1 val_2 estimate1 estimate2123456 000R 2014 12 2014-05-01 1 EPS ANN -1 -0.9 -0.9000 -0.95000 000R 2014 12 2014-07-31 2 EPS ANN -1.17 -1.04 -1.0400 -1.10500 000R 2014 12 2014-10-29 3 EPS ANN -1.07 -1.04 -1.0400 -1.05500 000R 2015 12 2015-03-19 4 EPS ANN -0.72 -0.53 -0.5300 -0.62500 000R 2015 12 2015-08-04 5 EPS ANN -0.68 -0.6 -0.6000 -0.64000 000R 2015 12 2015-10-29 6 EPS ANN -0.57 -0.54 -0.5400 -0.55500

joon1 · ‎01-29-2024

Dear Madam/Sir, I would like to remain only the last observation by multiple groups (ticker prd_yr anndats) ID is not coded by three multiple groups and last.id are not recognized using the following code. The sample dataset is attached. proc sort data=m2b; by ticker prd_yr anndats; run; proc freq data=m2b; tables ticker*prd_yr*anndats/noprint out=m2c; data m2c; set m2c; id=_n_; run; proc sort data=m2c; by ticker prd_yr anndats; run; data m2d; merge m2b m2c; by ticker prd_yr anndats; proc sort data=m2d; by ticker prd_yr anndats; run; data m3; set m2d; by ticker prd_yr anndats; if last.id; run; Any help will be highly appreciated. Sincerely, Joon1

joon1 · ‎10-06-2023

Thank you so much. Your code perfectly works.

joon1 · ‎10-06-2023

Thank you so much.

joon1 · ‎10-05-2023

Dear Madam/Sir, I would like to perform one operation by firm (gvkey) in dataset. Below is as-is dataset for only one firm and I like to drop observations after lead5 (from 2012) for every firm in the dataset. Do loop example mostly use array. In this case, I cannot use array. Any help will be highly appreciated. Thank you. data want; set have; do over gvkey if cyear gt lead5 then delete; end; run; gvkey cyear lead1 lead2 lead3 lead4 lead5 1161 2000 0 0 0 0 0 1161 2001 0 0 0 0 0 1161 2002 0 0 0 0 0 1161 2003 0 0 0 0 0 1161 2004 0 0 0 0 0 1161 2005 0 0 0 0 0 1161 2006 0 0 0 0 0 1161 2007 1 0 0 0 0 1161 2008 0 1 0 0 0 1161 2009 0 0 1 0 0 1161 2010 0 0 0 1 0 1161 2011 0 0 0 0 1 1161 2012 0 0 0 0 0 1161 2013 0 0 0 0 0 1161 2014 0 0 0 0 0 1161 2015 0 0 0 0 0 1161 2016 0 0 0 0 0 1161 2017 0 0 0 0 0 1161 2018 0 0 0 0 0 1161 2019 0 0 0 0 0 1161 2020 0 0 0 0 0 1161 2021 0 0 0 0 0

joon1 · ‎10-05-2023

Thanks for your kind reply. Data structure doesn't have date. So this function cannot be used.

joon1 · ‎10-05-2023

Dear Madam/Sir, I would like to define future years (t1 through t5) after the event year (cartel_eyear). The code using intnx function does not work. lead_t1=intnx(year, cartel_eyear, 1); lead_t2=intnx(year, cartel_eyear, 2); lead_t3=intnx(year, cartel_eyear, 3); lead_t4=intnx(year, cartel_eyear, 4); Any help will be highly appreciated. Sincerely, Joon1

joon1 · ‎06-23-2023

Dear Madam/Sir, I would like to add conditions when two datasets are merged. That is, how can we add conditions 'when cartel_byear=<cyear<=cartel_eyear then cartel_year=1; else cartel_year=0' when c2 (firm-year population) and ca3 are merged. proc sql undo_policy=none; create table c3 as select c2.*,ca3.cartel_byear, ca3.cartel_eyear from c2 left join ca3 on when cartel_byear=<cyear<=cartel_eyear then cartel_year=1; else cartel_year=0; (c2.gvkey=ca3.gvkey) ; quit; dataset ca3 Obs gvkey cartel_byear cartel_eyear12345 178698 2000 2006 21542 2004 2013 21542 2003 2014 21542 2004 2014 1004 1987 1995 dataset c2 (firm-year population) Obs GVKEY FYEAR12345 001000 1970 001000 1971 001000 1972 001000 1973 001000 1974 Any help will be highly appreciated. Sincerely, Joon

joon1 · ‎05-16-2023

Dear Madam/Sir, My goal is to have unique control firms (gvkey2) in a particular year that match between gvkey/merge_year in dataset1 and gvkey1/year in dataset 2. It could be multiple control firms (gvkey2) matched with gvkey in dataset 1 (=gvkey1 in dataset2) in a particular year. I attach a part of dataset2 (the original file contains 8 million observations) and dataset1. Any programming advice will be highly appreciated. Thanks Joon1

joon1 · ‎05-13-2023

Thank you for insightful comments, mkeintz. You are correct. To simplify programming, I have dropped observations that have different industry (SIC code) between gvkey1 and gvkey 2 in dataset 2 to have only observations with the same industry between gvkey1 and gvkey2. The industry issue is resolved. I attach a part of revised dataset2 (it is over 8 million observations). For your a second serious questions, my goal is to have unique control firms (gvkey2) in a particular year that match between gvkey/merge_year in dataset1 and gvkey1/year in dataset 2. It could be multiple control firms (gvkey2) matched with gvkey in dataset 1 in a particular year. Any programming advice will be highly appreciated. Thanks Joon1

joon1 · ‎05-12-2023

Dear Madam/Sir, I would like to pick control firms (gvkey2 in dataset2 with respect to gvkey1 and year) using treatment firms (gvkey and merge_year in the same industry_code in dataset 1). Have dataset 1 dataset 2 gvkey merge_year industry_code gvkey1 year gvkey2 Want new dataset (matching identifier: gvkey(dataset1)=gvkey1(dataset2), merge_year(dataset2)=year(dataset2)) gvkey merge_year industry_code gvkey2 Any programming idea will be highly appreciated. Two datasets are attached. Sincerely, Joon

Online Status	Offline
Date Last Visited	‎12-20-2024 12:57 PM

Re: Common auditor coding

Re: Common auditor coding

Re: Common auditor coding

Re: Common auditor coding

Re: Common auditor coding

Re: Common auditor coding

Re: Common auditor coding

Common auditor coding

Re: Select last quarter data using last.

Select last quarter data using last.

Array function

Re: Cannot import excel file (xls) into SAS 9.4 version in window 10 e...

Re: Coding previous years of CEO turnover event

WRDS data import issue

Re: Calculating the score average of other firms in the same year

Re: Calculating the score average of other firms in the same year

Calculating the score average of other firms in the same year

Re: The use of last.id by multiple groups

Re: The use of last.id by multiple groups

The use of last.id by multiple groups

Re: Same operation by group (firm)

Re: Same operation by group (firm)

Same operation by group (firm)

Re: Define future years

Define future years

Defining conditions when two datasets are merged

Control firm pick

Re: Control firm pick

Control firm pick