Numbering observations by group

[costasRO]

Hi All,

I have a dataset that looks like this:

Company     Manager  Date

A                       1              3/1999

A                       1               6/1999

A                       2               9/2000

B                       3                3/2000

B                       3                6/2000

B                       4                 9/2000

B                       4                  12/2000

I would like to create a counter variable (C) that equals 1 for the dates that the first manager is in each company, and then adds one for the dates the second manager is in the company, and so on.

The dataset i would like to have is

Company     Manager        Date                  C

A                       1              3/1999                1

A                       1               6/1999                1

A                       2               9/2000                 2

B                       3                3/2000               1

B                       3                6/2000                1

B                       4                 9/2000               2

B                       4                 12/2000              2

many thanks

Costas

[PaigeMiller]

/* UNTESTED CODE */
data want;
    set have;
    by company manager;
    if first.company then c=0;
    if first.manager then c+1;
run;

If you want tested code, please provide the data as SAS data step code (instructions).

[costasRO]

Thank you @PaigeMiller for the reply.

I attach here some of my data. wficn is the company, manager_id the manager and date.

The data I would like to have should be like:

wficn       manager_id      date             C

100352    1357               12/31/92          1

100352    1357              03/31/93          1

.                 .                          .                1

100352    8799             06/30/200         2

100352    8397             12/31/2000        3

100352    8397               06/30/2001      3

  .               .                           .                3

100352      1357            12/31/2010        4

.                    .                        .                 .

So basically the counter variable C remains fixed for the period that a given manager is in charge, and changes when a new manager comes in. Note that 

 manager 1357 returned to this company in 2010, and that this could have a different counter value (C=4) compared to the period that this manager was in place (C=1). 

The dataset has other wficn's and other manager_id's. The counter should start again when we have different wficn's.

Many thanks for your help!

Costas           

[costasRO]

Thank you @PaigeMiller for the reply.

I attach some sample data for you to see.

wficn is the company, manager_id and date. 

For this wficn the Counter should be 1 when from 12/31/1992-12/31/1999 when manager_id=1357, C=2 for 06/30/2000 when manager_id=8799,

then C=3 from 12/31/2000-06/30/2010 when manager_id=8397 and C=4 from 12/31/2010-end when manager_id=1357.

Nte that 1357 returns to the company but this should have a different C value.

The data has more wficn's and more manager id's. The C variable should reset for every company.

[I posted another reply, but not sure if it was done successfully, so i am doing it again].

many thanks

Costas 

[PaigeMiller]

Your data is not sorted by company and manager. If you do the sort, then my code should work.

[costasRO]

This code does not work because it gives the same value for manager 1357 throughout. The code should give C=1 for this manager the first time, and then when the manager returns the code should give this manager a different C value.

In the example I gave you, I would like to have 4 different C values, whereas your code only gives 3.

Thanks again. 

[PaigeMiller · Accepted solution]

data want;
    set test;
    prev_wficn=lag(wficn);
    prev_mgr=lag(manager_id);
    if wficn^=prev_wficn then c=0;
    if manager_id^=prev_mgr then c+1;
    drop prev_:;
run;

[Tom]

If the data is grouped but not actually ordered you can still use the BY statement.  Just add the NOTSORTED keyword.

data want;
  set have;
  by wficn manager_id notsorted;
  counter+1;
  if first.manager_id then counter=1;
run;

PS Why bother posting a SAS dataset for 210 numbers?  Just post text. It is much easier to deal with. See below

Spoiler

data have;
  input wficn manager_id date :yymmdd.;
  format date yymmdd10.;
cards;
100352 1357 1992-12-31
100352 1357 1993-03-31
100352 1357 1993-06-30
100352 1357 1994-03-31
100352 1357 1994-06-30
100352 1357 1994-12-31
100352 1357 1995-06-30
100352 1357 1995-12-31
100352 1357 1996-06-30
100352 1357 1996-12-31
100352 1357 1997-06-30
100352 1357 1997-12-31
100352 1357 1998-06-30
100352 1357 1998-12-31
100352 1357 1999-06-30
100352 1357 1999-12-31
100352 8799 2000-06-30
100352 8397 2000-12-31
100352 8397 2001-06-30
100352 8397 2001-12-31
100352 8397 2002-06-30
100352 8397 2002-12-31
100352 8397 2003-06-30
100352 8397 2003-12-31
100352 8397 2004-06-30
100352 8397 2004-09-30
100352 8397 2004-12-31
100352 8397 2005-06-30
100352 8397 2005-09-30
100352 8397 2006-03-31
100352 8397 2006-06-30
100352 8397 2006-09-30
100352 8397 2007-03-31
100352 8397 2007-06-30
100352 8397 2007-09-30
100352 8397 2007-12-31
100352 8397 2008-06-30
100352 8397 2008-09-30
100352 8397 2008-12-31
100352 8397 2009-03-31
100352 8397 2009-06-30
100352 8397 2009-09-30
100352 8397 2009-12-31
100352 8397 2010-03-31
100352 8397 2010-06-30
100352 1357 2010-12-31
100352 1357 2011-03-31
100352 1357 2011-06-30
100352 1357 2011-09-30
100352 1357 2011-12-31
100352 1357 2012-03-31
100352 1357 2012-06-30
100352 1357 2012-09-30
100352 1357 2012-12-31
100352 1357 2013-03-31
100352 1357 2013-06-30
100352 1357 2013-09-30
100352 1357 2013-12-31
100352 1357 2014-03-31
100352 1357 2014-06-30
100352 1357 2014-09-30
100352 1357 2014-12-31
100352 1357 2015-03-31
100352 1357 2015-06-30
100352 1357 2015-09-30
100352 1357 2015-12-31
100352 1357 2016-06-30
100352 1357 2016-09-30
100352 1357 2016-12-31
100352 1357 2017-03-31
;

[costasRO]

Thanks @Tom . Good to know about the NOTSORTED command.

I actually tried to use the macro that produces the data in txt form, but failed. Could you please share with me the macro you used to create the 'have' dataset below?

[Tom]

Just copy and paste.

Dump the values to the SAS log.

data _null_;
  set have;
  put wficn manager_id date :yymmdd10. ;
run;

Then paste them into your program.

If you want a macro for generating code from data try this one.

https://github.com/sasutils/macros/blob/master/ds2post.sas

It does a better job of making readable and workable code than the one in the SAS communities post.

Example from the macro defintion:

* Pull macro definition from GITHUB and dump code to the SAS log ;
filename ds2post url
  'https://raw.githubusercontent.com/sasutils/macros/master/ds2post.sas'
;
%include ds2post ;
%ds2post(sashelp.class)

PS Avoid using MDY or DMY order to display dates.  Either choice will confuse half of your audience.  Use YMD order or SAS's DATE format.