topic Numbering observations by group in SAS Programming

Numbering observations by group

costasRO — Fri, 27 May 2022 10:39:59 GMT

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

Re: Numbering observations by group

PaigeMiller — Fri, 27 May 2022 10:53:55 GMT

/* 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).

Re: Numbering observations by group

costasRO — Fri, 27 May 2022 12:51:06 GMT

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

Re: Numbering observations by group

costasRO — Fri, 27 May 2022 13:10:41 GMT

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

Re: Numbering observations by group

PaigeMiller — Fri, 27 May 2022 13:44:57 GMT

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

Re: Numbering observations by group

costasRO — Fri, 27 May 2022 13:56:29 GMT

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.

Re: Numbering observations by group

PaigeMiller — Fri, 27 May 2022 14:12:12 GMT

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;

Re: Numbering observations by group

Tom — Fri, 27 May 2022 17:43:09 GMT

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

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
;

Re: Numbering observations by group

costasRO — Mon, 30 May 2022 07:11:14 GMT

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?

Re: Numbering observations by group

Tom — Mon, 30 May 2022 14:48:29 GMT

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.