topic Re: Keep multiple rows and same variable and different variable in SAS Programming

Keep multiple rows and same variable and different variable

teamlinerek — Tue, 09 Apr 2024 16:44:27 GMT

Hi All,

I am wanting to keep only observations where this is both a CEO and a CFO for every GVKEYYEAR.

I HAVE the following:

Name	CEOANN	CFOANN	GVKEYYEAR
Batman	CEO		2008185
Robin		CFO	2008185
Mickey	CEO		2001053
Mikey		CFO	2088087
Leo	CEO		2088087
Goofy		CFO	2109087
Superman	CEO		2089171
Peanut Butter	CEO		1089721
Jelly		CFO	1089721

What I WANT is the following:

Name	CEOANN	CFOANN	GVKEYYEAR
Batman	CEO		2008185
Robin		CFO	2008185
Mikey		CFO	2088087
Leo	CEO		2088087
Peanut Butter	CEO		1089721
Jelly		CFO	1089721

What code may help me do this?

Thank you!

Re: Keep multiple rows and same variable and different variable

yabwon — Tue, 09 Apr 2024 16:56:10 GMT

data have;
infile cards dlm="|";
input Name :$32. (CEOANN CFOANN) (:$3.) GVKEYYEAR;
cards;
Batman|CEO| |2008185
Robin| |CFO|2008185
Mickey|CEO| |2001053
Mikey| |CFO|2088087
Leo|CEO| |2088087
Goofy| |CFO|2109087
Superman|CEO| |2089171
Peanut Butter|CEO| |1089721
Jelly| |CFO|1089721
;
run;
proc print;
run;

data want;
  call missing(check1,check2);
  do _N_=1 by 1 until(last.GVKEYYEAR);
    set have;
    by GVKEYYEAR notsorted;
    check1+(CEOANN='CEO');
    check2+(CFOANN='CFO');
  end;

  do _N_=1 to _N_;
    set have;
    if check1 & check2 then output;
  end;
  drop check1 check2; 
run; 
proc print;
run;

Re: Keep multiple rows and same variable and different variable

ballardw — Tue, 09 Apr 2024 17:42:16 GMT

This works for example data provided

proc sql;
   create table want as
   select b.* 
   from 
     ( select gvkeyyear 
      from  have
      where not missing(ceoann) or not missing(cfoAnn)
      group gvkeyyear
      having count(gvkeyyear)=2
     ) as a
     left join have as b
     on a.gvkeyyear=b.gvkeyyear
   ;  
quit;

The Where may not be needed but might if you have other observations in your data for the gvkeyyear related to other data not shown in your example.

If you built that set to have two separate variables for Ceoann and CfoAnn you might consider a single variable with the position value, such as CEO and CFO. That is quite often more flexible in the long run than adding a bunch of variables for each level of meaning.

Re: Keep multiple rows and same variable and different variable

mkeintz — Wed, 10 Apr 2024 01:53:39 GMT

Your data appears to be already presented either as a single record for a given GVKEYYEAR, or a pair of records for a GVKEYYEAR. In the latter case, one CEO appears and one CFO appears. If that is the case, then just delete all the singletons and keep all the pairs.

data have;
infile cards dlm="|";
input Name :$32. (CEOANN CFOANN) (:$3.) GVKEYYEAR;
cards;
Batman|CEO| |2008185
Robin| |CFO|2008185
Mickey|CEO| |2001053
Mikey| |CFO|2088087
Leo|CEO| |2088087
Goofy| |CFO|2109087
Superman|CEO| |2089171
Peanut Butter|CEO| |1089721
Jelly| |CFO|1089721
run;
data want;
  set have ;
  by gvkeyyear notsorted;
  if not(first.gvkeyyear=1 and last.gvkeyyear=1); 
run;

Re: Keep multiple rows and same variable and different variable

Ksharp — Wed, 10 Apr 2024 02:22:05 GMT

data have;
infile cards dlm="|";
input Name :$32. (CEOANN CFOANN) (:$3.) GVKEYYEAR;
cards;
Batman|CEO| |2008185
Robin| |CFO|2008185
Mickey|CEO| |2001053
Mikey| |CFO|2088087
Leo|CEO| |2088087
Goofy| |CFO|2109087
Superman|CEO| |2089171
Peanut Butter|CEO| |1089721
Jelly| |CFO|1089721
;
run;

proc sql;
create table want as
select * from have 
 group by GVKEYYEAR
  having sum(CEOANN='CEO') and sum(CFOANN='CFO');
quit;