@Reeza, thanks for this helpful suggestion. The code works very well. You are right in regards to being careful how SAS stores date variables. I will look into it now. Thanks again.  ... View more

Sounds good, @Shmuel. Maybe I will post a new question and ask whether there could be a more efficient code compared to mine. Thanks for this helpful suggestion.  ... View more

Here is my updated code and it works actually well. I will post below a diagram to show what I was doing.  DATA have; INPUT Mgrno Group ID Industry $ Source; DATALINES; 9999 1 00462610 1842 A 9999 1 01737210 1842 A 9999 1 12690310 2832 A 8888 1 12690310 2832 T 8888 1 12690311 2832 T 8888 1 37158620 2124 T 9999 1 23298321 4238 A 9999 1 53228424 4238 A 8888 1 53228424 4238 T 8888 1 23298321 4238 T 9999 1 12290311 1233 A 8888 1 01737211 1233 T 8888 1 99998881 1233 T 7777 2 12690310 2832 A 3333 2 32690311 2832 T 7777 2 37158620 2124 A 7777 2 38138222 3321 A 3333 2 44134422 3321 T 7777 2 53228424 4238 A 3333 2 77233421 4238 T 3333 2 12290311 1233 T 3333 2 93234320 1333 T ; RUN; proc sort data=work.have nouniquekeys uniqueout=singles out=dup; by group id; quit; proc sort data=work.have out=list_noduplicates nodupkey; by group id; quit; data both; set dup; cont=1; run; *The firms controlled by both managers are control; proc sql; create table distinct_both as select distinct group, industry, 1 as control from both; quit; *Here I am selecting the Managers with a Source=A since I will compare the firms this Manager controls with the firms the other Manager controls. T stands for Target and A for Acquirer.; data all_acq; set work.list_noduplicates; if Source='A'; run; *Here I am selecting the Managers with a Source=T since I will compare the firms this Manager controls with the firms the other Manager controls; data all_tar; set work.list_noduplicates; if Source='T'; run; *Distinct cases start here, first compare distinct industries of target to all industries of the acquirer; data distinct_tar; set singles; if source='T'; distinct_tar=1; industry_target=industry; cusip_tar=ncusip; run; proc sql; create table treated_tar_V1 as select * from all_acq as a left join distinct_tar as b on a.industry=b.industry_target and a.group=b.group; quit; *These are some of the treated firms; data treated_tar_V2(keep=group industry); set treated_tar_V1; if industry_target^=.; run; proc sort data=treated_tar_V2 out=distinct_target nodupkey; by group industry; run; *Second compare distinct firms controlled only by acquirer to all firms controlled by target; data distinct_acq; set singles; if source='A'; distinct_acq=1; industry_acq=industry; cusip_acq=ncusip; run; proc sql; create table treated_acq_V1 as select * from all_tar as a left join distinct_acq as b on a.industry=b.industry_acq and a.group=b.group; quit; *These are the treated firms; data treated_acq_V2(keep=group industry); set treated_acq_V1; if industry_acq^=.; run; proc sort data=treated_acq_V2 nodupkey; by group industry; run; data all_treated(keep=group industry treated); set distinct_both treated_tar_V2 treated_acq_V2; treated=1; run; proc sql; create table all_treated_distinct as select distinct group, industry, 1 as treated from all_treated; quit; ... View more

Hi again, @kiranv_, @Tom, and @Reeza, I have just tried to use the new variable in an artimethic computation even though I have change the Char format to a Numeric format, but it did not work. Even if I change it to Best format, it still doesn't work. The reason why I have tried to change it again to Best is that there was another variables that is in the Best format and the computation worked well.   data have(rename= (ex_post_date1= ex_post_date)); set have; ex_post_original=ex_post_date; ex_post_date1 = input(ex_post_date,date9.); drop ex_post_date; format ex_post_date1 yymmddn8.; run; data have; set have; numeric=put(ex_post_date1,best8.); format ex_post_date1 best8.; run; *Here is the math computation; data want; set have; year=int(ex_post_date/10000); month=int(ex_post_date/100)-year*100; day=ex_post_date-year*10000-month*100; NEED_num=mdy(month,day,year); format NEED_num mmddyy10.; drop year month day; run; ... View more

Exactly, that is correct. The Count_MGRS is to count the number of managers in a given Group-Industry. I will shortly post the new code I thought of. I think that works better.  ... View more

Thanks @kiranv_, your code worked very well. However, since @Tom's reply was first and his worked as well, I accepted his answer. Thank you very to you both for your helpful answers.  ... View more

                                              The CONTENTS Procedure:   Alphabetic List of Variables and Attributes # Variable Type Len Format Informat Label  Acquiror_mgrno1 Num 8 BEST.   Acquiror_mgrno1 Acquiror_name Char 27 $27. $27. Acquiror_name Announcement Char 9 $9. $9. Announcement Announcement_date Num 8 BEST.   Announcement_date Effective Char 9 $9. $9. Effective Effective_date Num 8 BEST.   Effective_date Ex_post_date Char 10 $10. $10. Ex_post_date Merger_ID Char 2 $2. $2. Merger_ID Mgrno Char 8 $8. $8. Mgrno Note 1 Char 141 $141. $141. Note 1 Note 2 Char 99 $99. $99. Note 2 Note 3 Char 20 $20. $20. Note 3 Note 4 Target: Char 47 $47. $47. Note 4 Target: Note Ex Post Char 174 $174. $174. Note Ex Post Target ends in Char 10 $10. $10. Target ends in Target_mgrno1 Num 8 BEST.   Target_mgrno1 Target_name Char 29 $29. $29. Target_name Who continues? Char 28 $28. $28. Who continues?   ... View more

Here is the information @kiranv_:   ... View more

Thank @soumri, but I am getting the following error: NOTE: Invalid numeric data, Ex_post_date='31MAR1996' , at line 64 column 25. NOTE: Invalid numeric data, Ex_post_date='31MAR1996' , at line 64 column 46. NOTE: Invalid numeric data, Ex_post_date='31MAR1996' , at line 64 column 65. WARNING: Limit set by ERRORS= option reached. Further errors of this type will not be printed. Acquiror_name=APP Acquiror_mgrno1=9999 Target_name=SBZ Target_mgrno1=8888 Announcement_date=19941128 Effective_date=19970412 Announcement=28Nov1994 Effective=12Apr1997 Ex_post_date=31MAR1999 Merger_ID=AS Who continues?=Acquiror Mgrno=49240 Target ends in=30SEP1996 Note Ex Post= Note 1= Note 2= Note 3= Note 4 Target:= new_date=... _ERROR_=1 _N_=20 NOTE: Missing values were generated as a result of performing an operation on missing values. Each place is given by: (Number of times) at (Line):(Column). 60 at 64:20 60 at 64:40 60 at 64:61 NOTE: There were 60 observations read from the data set WORK.IMPORT1. NOTE: The data set WORK.IMPORT1 has 60 observations and 19 variables. NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds ... View more

@SASKiwi when I took your code to the actual data, I am getting the below error. The code I have used:  *This is what I have tried; data IMPORT1; set IMPORT1; newdate = input(put(ex_post_date,DATE9.),yymmddn8.); run; *But I have also tried; data IMPORT1; set IMPORT1; format ex_post_date date9.; newdate1=datepart(ex_post_date); run; This is the error: 1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK; 61 62 data IMPORT1; 63 set IMPORT1; 64 format ex_post_date date9.; ______ 484 NOTE 484-185: Format $DATE was not found or could not be loaded. 65 newdate1=datepart(ex_post_date); 66 run; NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column). 65:21 NOTE: Invalid numeric data, Ex_post_date='30SEP1984' , at line 65 column 21. ... View more

@Reeza, the type is Char and the format (and informat) is $10.  ... View more

@Shmuel, thanks so much for all of your helpful replies. I am learning a lot from you on how to improve the code and ask a question in a better, clearer way! I will definetly make more often use of diagrams in the future rather than posting long texts. Regarding point (1), apologies I just realized that the industry code for that particular firm was correctly written in the diagram.  Regarding point (2), the table captures well the data except that the rows are by Firm ID + MGRNO. So there can be rows where there is the same Firm ID with different MGRNOs. Thanks again. Best regards   ... View more

That was such a wonderful SQL code, @gamotte! I really like it. Thanks so much for proposing an alternative way of doing it. Cheers ... View more

@Shmuel, thanks for your helpful edit/comment in my reply. In the meantime, I was able to write a code that produces the output that is needed. I tried to figure out a way to just keep the rows where Control=1. Is there an alternative way of doing this? I have tried to construct the steps such that I am only left with the control=1 rows. I think the sample captures fairly well all possible cases (as depicted in the picture I have posted earlier).  I am really in rush since I have to prepare a report (for a academic research project) due this Friday that relies primarily on this identification. Any suggestion will be greatly appreciated! 🙂    DATA want; INPUT Mgrno Group ID Industry Control; DATALINES; 9999 1 00462610 1842 0 9999 1 01737210 1842 0 9999 1 12690310 2832 0 8888 1 12690310 2832 0 8888 1 12690311 2832 0 8888 1 37158620 2124 0 9999 1 23298321 4238 1 9999 1 53228424 4238 1 8888 1 53228424 4238 1 8888 1 23298321 4238 1 9999 1 12290311 1233 1 8888 1 01737211 1233 1 8888 1 99998881 1233 1 7777 2 12690310 2832 1 3333 2 32690311 2832 1 7777 2 37158620 2124 0 7777 2 38138222 3321 1 3333 2 44134422 3321 1 7777 2 53228424 4238 1 3333 2 77233421 4238 1 3333 2 12290311 1233 0 3333 2 93234320 1333 0 ; RUN; *Deleting the industries with only one MGRNO; proc sql; create table work.output_V1 as select * from want group by group, industry having count(distinct mgrno) ne 1; quit; *Counting how many IDs a particular MGRNO controls in a given industry; proc summary nway; class mgrno group industry; var ID; output out=number(drop=_:) n=count_identifer; run; *Adding back to the sample the num of IDs a manager controls in a given industry; proc sql; create table work.output_V2 as select * from work.output_V1 as a left join work.number as b on a.mgrno=b.mgrno & a.group=b.group & a.industry=b.industry; quit; *Getting the duplicates; proc sort data=work.output_V2 nouniquekeys uniqueout=singles out=dup; by group industry id; quit; *Identifying the duplicates; data work.duplicates; set dup; duplicates=1; run; proc sql; create table duplicates_final as select distinct group, industry, id, duplicates from duplicates quit; proc sql; create table output_V3 as select * from work.output_V2 as a left join work.duplicates_final as b on a.group=b.group & a.industry=b.industry & a.id=b.id; quit; proc stdize data = work.output_V3 out=work.output_V3 reponly missing=0; var duplicates; quit; proc sql; delete from work.output_V3 where count_identifer=1 & duplicates=1; quit; proc sql; create table work.output_Final as select * from work.output_V3 group by group,industry having count(distinct mgrno) ne 1; quit;     ... View more