Hi, You can use the following code: /* First step: Build the "appears_as_id_in_have" indicator, for each id_adj group by year and firm*/
proc sql noprint;
create table list_ext as
select c.year,
c.firm,
a.id,
a.id_adj,
a.use,
ifn(id_adj in (select b.id from have as b where c.year=b.year and c.firm=b.firm),1,0)
as appears_as_id_in_have,
a.use*(calculated appears_as_id_in_have) as indic_treat
from list as a left join have as c
on a.id=c.id
order by year,firm,id;
quit;
/* Second step: determine "treat" based on the previous indicators */
proc sql noprint;
create table output as
select distinct
year,firm,id,treat
rom (select *,
sum(use) as sum_use,
sum(indic_treat) as sum_indic_treat,
case
when (calculated sum_use=0) then 4
when (calculated sum_use>0 and calculated sum_indic_treat=0) then 5
else 0
end as treat
from list_ext
group by year, firm, id);
quit; Let me know if you have questions ! Regards, JD
... View more