exclude all 'company_name' variable if one of the 'year' variable smaller than 2009.

[France]

Dear all,

how can I exclude all 'company_name' variable if one of the 'year' variable( belongs to the same 'company_name' group) smaller than 2009.

data old;<br />input <br />company_name $50. <br />year 50.<br />;<br />datalines;
A, 1990<br />A, 2011<br />B, 2019
;
run;<code></code>

 I expect to get the result like

B, 2019

 as one 'year' variable of company_name 'A' variable is 1990 (which is smaller than 2009), so, I exclude all A company,

could you please give me some suggestions about this?

thanks in advance

[Kurt_Bremser]

Do a subselect for the exclusion values, and use that in a where:

proc sql ;
create table want as
select *
from old
where company_name not in (
  select company_name
  from old
  where min(year) < 2009
  group by company_name
);
quit;

(untested, posted from tablet)

[Kurt_Bremser]

Since I'm now back in office, I could test it.

First, it is always a VERY GOOD IDEA to test your own datastep code for example data before posting it here. Testing is not a crime, it won't make you blind or cause your hair to fall out.

Your code would not produce any data, instead it caused an ERROR message.

I fixed it to this:

data old;
infile datalines dlm=',';
input
  company_name $
  year
;
datalines;
A, 1990
A, 2011
B, 2019
;
run;

After that, I found my own code was using the summary function incorrectly, so I had to restructure the subselect to using a HAVING clause:

proc sql ;
create table want as
select *
from old
where company_name not in (
  select company_name
  from old
  group by company_name
  having min(year) < 2009
);
quit;

This creates your expected result.