topic exclude all 'company_name' variable if one of the 'year' variable smaller than 2009. in SAS Programming

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

France — Sun, 17 Feb 2019 17:16:03 GMT

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; input company_name $50. year 50. ; datalines; A, 1990 A, 2011 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

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

Kurt_Bremser — Sun, 17 Feb 2019 17:53:14 GMT

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)

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

Kurt_Bremser — Mon, 18 Feb 2019 08:08:21 GMT

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.