This was super helpful. Quite frankly, I was amazed on how efficiently the code is written (my code was super long, but you summed it up in just one SQL statement). Our SAS community is amazing and super helpful! I really appreciate all of your replies. This is such a wonderful websites and allows one to learn SAS much faster! I am loving it.  Thanks again.  ... View more

@Shmuel, I think the earlier code works correctly so we can ignore the second (simplified) code since it produces incorrect results. I think I am failing to exclude cases that should not be in the final sample. It seems like identifying directly the treatment group is a safer way compared to deleting the control group to identify the treatment (indirect way). If you prefer and have not much time, I can accept your modification (i.e., simplification) of my code as the answer or just simply wait until you may have something. A colleague of mine wrote another code that produces almost the same outcome as the first code (only off by one observation).  To be frank, I have to state that without your comments I would have not been able to go to the correct direction. So your earlier replies have really helped out to come up with these codes! Thank you!! ... View more

@ChrisNZ, thank you!!! That was such a helpful reply, the code works very well after chaning just a few lines. Would you mind updating your code above so that I can accept it as the answer since with the modification your code identifies the correct groups.  Here is your code with a few (really) minor changes: proc sql; create table outcome as select a.MERGER_ID, a.FIRM_ID ,a.SICH ,a.SOURCE, b.FIRM_ID ,count(a.FIRM_ID) as CNT_IN_P ,sum(b.FIRM_ID ne .) as CNT_IN_P1 ,sum(a.SOURCE='A') as CNTA_IN_P ,sum(a.SOURCE='A' and b.FIRM_ID ne .) as CNTA_IN_P1 ,sum(a.SOURCE='T') as CNTT_IN_P ,sum(a.SOURCE='T' and b.FIRM_ID ne .) as CNTT_IN_P1 ,case when calculated CNT_IN_P =calculated CNT_IN_P1 then 'A' when calculated CNTA_IN_P =calculated CNTA_IN_P1 and calculated CNTT_IN_P1=0 then 'B' when calculated CNTT_IN_P =calculated CNTT_IN_P1 and calculated CNTA_IN_P1=0 then 'C' when calculated CNT_IN_P > calculated CNT_IN_P1 and calculated CNTA_IN_P1 > 0 and calculated CNTT_IN_P1 > 0 then 'D' when calculated CNT_IN_P > calculated CNT_IN_P1 and calculated CNTA_IN_P1 > 0 and calculated CNTT_IN_P1 = 0 then 'E' when calculated CNT_IN_P > calculated CNT_IN_P1 and calculated CNTA_IN_P1 = 0 and calculated CNTT_IN_P1 > 0 then 'F' else 'X' end as GROUP from PERIOD a left join PERIOD1 b on a.FIRM_ID = b.FIRM_ID and a.SICH = b.SICH and a.MERGER_ID= b.MERGER_ID group by a.MERGER_ID, a.SICH having calculated GROUP ne 'X' and b.FIRM_ID ne . order by a.MERGER_ID, a.SICH, a.FIRM_ID; quit; It works really well, and thank to you I have just learned how to use "calculated" in the case statement. I haven't used this handy statement before so it's great to have it in my "SQL toolkit".  Edit: I have only made a few minor changes in the "case when" statement. Otherwise, it is exactly the same code as you have.   ... View more

Thanks for this question, @art297. After deleting one observation from have2 (the other firm_id in the same industry) that I copied accidently to follow the diagram (I was off by two observations as you have pointed out, so a big thank you to you!), I think it would make sense now. I have updated the the two datasets above and verified all rows again. Now I think all looks good. Sorry for the confusion, but you are right that with the earlier dataset, firm_id = 99883211 should have not been assigned to "E". I wanted to cover all possible cases by creating a toy sample, but that was one of the bugs that I just fixed. Now, I think it would make sense with the above sample.  ... View more

Thanks for your reply, @art297. I have manually identified those rows. However, the code below is what I have tried.  Update 1: Thank to you @art297, I have just noticed that 3 rows in the previous "want" dataset were incorrectly identified. I have updated the "want" dataset. Sorry for this "human error".  Update 2: I think there is a major bug after I have made a minor change in the code. Before making a few minor changes, I was able to get very close except of missing two groups.        proc sql; create table step1 as select * from have1 as a right join have2 as b on (a.merger_id=b.merger_id) & (a.firm_id=b.firm_id); quit; data step1; set step1; survive=1; run; proc stdize data = work.step1 out=work.step1 reponly missing=0; var survive; run; *Now I will count the firms for a given Merger_ID-SICH, including only A and only T ones; proc summary nway; class merger_id SICH; var firm_id; output out=count_all n=count_all; run; data only_t; set step1; if source='T'; run; proc summary nway; class merger_id SICH; var firm_id; output out=count_t n=count_t; run; data only_a; set step1; if source='A'; run; proc summary nway; class merger_id SICH; var firm_id; output out=count_a n=count_a; run; proc sql; create table step2_count_V1 as select a.*, b.count_all from step1 as a left join count_all as b on (a.merger_id=b.merger_id) & (a.sich=b.sich); quit; proc sql; create table step2_count_V2 as select a.*, b.count_t from step2_count_V1 as a left join count_t as b on (a.merger_id=b.merger_id) & (a.sich=b.sich); quit; proc sql; create table step2_count_V3 as select a.*, b.count_a from step2_count_V2 as a left join count_a as b on (a.merger_id=b.merger_id) & (a.sich=b.sich); quit; proc stdize data = work.step2_count_V3 out=work.step2_count_V3 reponly missing=0; var count_all count_t count_a; run; *Now I will count the surviving firms for a given Merger_ID-SICH, including only A and only T ones; proc sql; create table have2_source as select a.*, b.Source from have2 as a left join have1 as b on (a.merger_id=b.merger_id) & (a.firm_id=b.firm_id); run; data source_notmissing; set have2_source; if source^=' '; run; proc summary nway; class merger_id SICH; var firm_id; output out=count_surv_all n=count_surv_all; run; data only_surv_t; set source_notmissing; if source='T'; run; proc summary nway; class merger_id SICH; var firm_id; output out=count_surv_t n=count_surv_t; run; data only_surv_a; set source_notmissing; if source='A'; run; proc summary nway; class merger_id SICH; var firm_id; output out=count_surv_a n=count_surv_a; run; proc sql; create table surv_count_V1 as select a.*, b.count_surv_all from source_notmissing as a left join count_surv_all as b on (a.merger_id=b.merger_id) & (a.sich=b.sich); quit; proc sql; create table surv_count_V2 as select a.*, b.count_surv_t from surv_count_V1 as a left join count_surv_t as b on (a.merger_id=b.merger_id) & (a.sich=b.sich); quit; proc sql; create table surv_count_V3 as select a.*, b.count_surv_a from surv_count_V2 as a left join count_surv_a as b on (a.merger_id=b.merger_id) & (a.sich=b.sich); quit; proc stdize data = work.surv_count_V3 out=work.surv_count_V3 reponly missing=0; var count_surv_all count_surv_t count_surv_a; run; proc sql; create table step2_with_counts as select * from step2_count_V3 as a left join surv_count_V3 as b on (a.merger_id=b.merger_id) & (a.firm_id=b.firm_id) & (a.source=b.source); quit; data step3; set step2_with_counts; if survive=1; run; proc sql; create table final as select *, case when (count_all=count_surv_all) then 'A' when (count_all^=count_surv_all) & (count_a=count_surv_a) then 'B' when (count_all^=count_surv_all) & (count_t=count_surv_t) then 'C' when (count_all^=count_surv_all) & count_surv_t>0 & count_surv_a>0 then 'D' when (count_all^=count_surv_all) & count_surv_t=0 & count_surv_a>0 then 'E' when (count_all^=count_surv_all) & count_surv_t>0 & count_surv_a=0 then 'F' end as Group from step3 group by merger_id, SICH; quit; ... View more

Below I am just adding the firms from "have2" (i.e., period T+1 firms) to "have1" (i.e., period T firms). Before that, I add an identifier in "have2" so that I can track which firms actually survive in "have2". I think that will help to construct the groups. But here I am stuck since I am not sure how to compare within a given industry (i.e., SICH) whether there are some missing "survivors." I will try out a code and submit a follow-up reply. data have2; set have2; survive=1; run; proc sql; create table step1 as select * from have1 as a left join have2 as b on (a.merger_id=b.merger_id) & (a.firm_id=b.firm_id); quit; proc stdize data = work.step1 out=work.step1 reponly missing=0; var survive; run; Possibly something as follows may work, but I am not sure how to write the conditions in the "when statement". Please excuse that I am out of the "case" statement condition (I know I should have an "end as" statement), but just for illustrative purposes see below:   proc sql; create table step2 as select *, case when ... Group='A' when ... Group='B' ... when ... Group='E' group by merger_id, sich from step1; quit; ... View more

You are right that the logic factor is beyong SAS, but I would argue that your better at logic than me. 🙂 Plus, your help with finding alternative ways to solve the issues has been very helpful. You are honestly a great SAS mentor and teacher! I benefit a lot from your comments. ... View more

You are certainly right, @Shmuel. I agree with you that having the correct logic is key. I think the first code should be correct since in the other one I may have not deleted some firms that were supposed to be deleted. So the logic in the second one may be incorrect.  ... View more

@Shmuel, I just found an alternative way that mainly relies on proc summary and proc sql. This code is much easier to follow. First, I identify the industries where there is only one manager, and then identify the industries where there is only one firm. It's actually pretty simple this way although I went backwards by identifying the treated=0 (or control=0) group. The other codes identifies the treated=1 group and both ways produce the same result, which is great! Made me quite happy. 🙂 Update: Apparently, I should have not been so quick with being happy about the code. It turns out that when I use the code below in the actual data, I get very different results. The logic of the original code is exactly as the one in the diagram. In the code below, I tried a different way, but it turns out that this did not work well. I am still not sure where the difference is though.  data have; set have; run; /*Counting the number of firms within a given group-industry pair*/ proc summary nway; class group industry; var id; output out=count_firms(drop=_:) n=count_firms; run; /*Adding it back to the original sample*/ proc sql; create table work.have1 as select * from work.have as a left join work.count_firms as b on a.group=b.group & a.industry=b.industry; quit; proc sort data=have1 out=dup nodupkey; by group industry mgrno; quit; /*Counting the number of distinct managers within a given group-industry pair*/ proc summary nway; class group industry; var MGRNO; output out=count_managers(drop=_:) n=count_managers; run; /*Adding it back to the original sample*/ proc sql; create table work.output_V2 as select * from work.have1 as a left join work.count_managers as b on a.group=b.group & a.industry=b.industry; quit; /*Whenever there is a single manager or whenever there is only one firm in a given group-industry , treated=0*/ data work.output_V3; set work.output_V2; if count_firms=1 or count_managers=1 then treated=0; else treated=1; run; proc sql; create table all_treated_distinct1 as select distinct group, industry, treated from output_V3 where treated=1; quit; proc sql; create table want as select * from have as a left join all_treated_distinct1 as b on (a.group=b.group) & (a.industry=b.industry); quit; proc stdize data = work.want out=work.want reponly missing=0; var treated; run;   ... View more

@Tom, thank you so much for taking your time to go over how formatting of variables works in SAS. I have stopped using SAS for a long time and just moved a few weeks ago back. I was not familiar with formatting of variables, but this reply was really helpful! Now I should be good with formatting! Thanks again. ... View more

Your replies are always very helpful, @Shmuel. Thank you. I think the same result may be obtained by looking at a given group and selecting the industries where there are only two managers. It actually is much simpler this way than comparing the industries of the two managers within a given group. I will try to write a SQL code to try it out.  ... View more

  Here is what I have: 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;     Here is the want dataset: 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; Here is my code: 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

@kiranv_, thanks for your reply. The code didn't work, unfortunately, work. However, @Reeza's suggestion works well.  Thanks to you both again. ... View more