topic creating dummy by group through time in SAS Programming

creating dummy by group through time

AmirSari — Sun, 05 Sep 2021 01:39:07 GMT

Hi all,

I have the following dataset and I am trying to create a dummy variable that takes one for execid that held a CEO position at another firm and zero otherwise. Thank you!

data have;
  input execid companyid role $ date yymmdd8.;
  format date date9.;
cards;
1 21 Chairman 20191128
1 21 CEO 20170214
1 13 CEO 20160530
1 13 CFO 20140622
1 18 Director 20110831
1 18 Director 20091220
1 18 CEO 20070217
1 18 CEO 20060302
1 42 CFO 20050410
1 42 Advisor 20040425
1 36 CFO 20020609
1 36 Member 20021025
1 36 Member 20000612
2 31 Advisor 20190515
2 31 Advisor 20160813
2 29 Chairman 20120522
2 28 Director 20110524
2 28 CEO 20090919
2 26 CFO 20080101
2 26 CFO 20060321
;run;

Here is my desired dataset.

execid	companyid	role	date	CEO_Dummy
1	21	Chairman	20191128	1
1	21	CEO	20170214	1
1	13	CEO	20160530	1
1	13	CFO	20140622	1
1	18	Director	20110831	0
1	18	Director	20091220	0
1	18	CEO	20070217	0
1	18	CEO	20060302	0
1	42	CFO	20050410	0
1	42	Advisor	20040425	0
1	36	CFO	20020609	0
1	36	Member	20021025	0
1	36	Member	20000612	0
2	31	Advisor	20190515	1
2	31	Advisor	20160813	1
2	29	Chairman	20120522	1
2	28	Director	20110524	1
2	28	CEO	20090919	1
2	26	CFO	20080101	1
2	26	CFO	20070321	1
2	31	Advisor	20060813	1
2	29	Chairman	20050522	1
2	28	Director	20040524	1
2	28	CEO	20030919	0
2	26	CFO	20030101	0
2	26	CFO	20010321	0

Re: creating dummy by group through time

japelin — Sat, 04 Sep 2021 09:31:52 GMT

How about this code.

data want;
  set have;
  ceo_dummy=mod(floor(rand('UNIFORM', 1, 100)),2);
run;

Re: creating dummy by group through time

AmirSari — Sat, 04 Sep 2021 17:35:10 GMT

Thank you for your reply. This does not produce what I am looking for. I want an indicator variable that is one if the executive, i.e. execid, held a CEO position in his/her prior experience and zero otherwise.

Re: creating dummy by group through time

Patrick — Sun, 05 Sep 2021 01:02:09 GMT

Why in your desired result does the earliest entry for execid=2 start with a CEO_Dummy=1?

Should the ceo_dummy become 1 if the role has been 'CEO' in any of the previous companies or only if it was the most recent previous one?

Below above two options implemented - but none matches your desired result. Can you please specify the required logic further.

data have;
  input execid companyid role $ date yymmdd8. expected_ceo_flg :$1.;
  format date date9.;
cards;
1 21 Chairman 20191128 1
1 21 CEO 20170214 1
1 13 CEO 20160530 1
1 13 CFO 20140622 1
1 18 Director 20110831 0
1 18 Director 20091220 0
1 18 CEO 20070217 0
1 18 CEO 20060302 0
1 42 CFO 20050410 0
1 42 Advisor 20040425 0
1 36 CFO 20020609 0
1 36 Member 20021025 0
1 36 Member 20000612 0
2 31 Advisor 20190515 1
2 31 Advisor 20160813 1
2 29 Chairman 20120522 1
2 28 Director 20110524 1
2 28 CEO 20090919 1
2 26 CFO 20080101 1
2 26 CFO 20060321 1
2 31 Advisor 20160813 1
2 29 Chairman 20120522 1
2 28 Director 20110524 1
2 28 CEO 20090919 0
2 26 CFO 20080101 0
2 26 CFO 20060321 0
;

proc sort data=have;
  by execid date;
run;

data want(drop=_:);
  set have;
  by execid companyid notsorted;
  length _ceo_any _ceo_last $1;
  retain _ceo_any _ceo_last;

  length ceo_any_flg ceo_last_flg $1;
  retain ceo_any_flg ceo_last_flg;


  if first.execid then
    do;
      _ceo_any='0';
      _ceo_last='0';
      ceo_any_flg=_ceo_any;
      ceo_last_flg=_ceo_last;
    end;

  if not first.execid and first.companyid then
    do;
      ceo_any_flg=_ceo_any;
      ceo_last_flg=_ceo_last;
      _ceo_last='0';
    end;

  if role='CEO' then
    do;
      _ceo_any='1';
      _ceo_last='1';
    end;
run;

Re: creating dummy by group through time

AmirSari — Sun, 05 Sep 2021 01:07:08 GMT

The earliest entry for execid=2 starts with a CEO_Dummy=1 because this executive held a CEO role in companyid=28 in 2009. I want the dummy to become 1 if the role has been 'CEO' in any of the previous companies.

Re: creating dummy by group through time

Patrick — Sun, 05 Sep 2021 01:40:53 GMT

@AmirSari wrote:
The earliest entry for execid=2 starts with a CEO_Dummy=1 because this executive held a CEO role in companyid=28 in 2009. I want the dummy to become 1 if the role has been 'CEO' in any of the previous companies.

The role held in companyid=28 was AFTER the role held in companyid=26 so that's not PREVIOUS company.

Re: creating dummy by group through time

AmirSari — Sun, 05 Sep 2021 01:47:24 GMT

You are absolutely right! I messed up the dates. My bad, sorry1 I corrected my post.
Thank you for your solution. It works the way I wanted.