Creation of up-counting ID variables per group, not according to distinct variables

[Sinistrum]

Dear community,

I would be very glad, if you could help concerning the following issue.

Please consider the following table (as also attached as sas7bdat) I am faced with

, as well as the table I do want to end up with:

The scenario, thus, is the following:

I have a table with companies. This table contains "beginning_date" as well as "ending_date" - this variables define, from "when to when" the row-information hold valid. As an example, I added information towards the "quality" of the offered product.

Object of interest is the "manager" - I want to end up with a data set, listing up

which manager

works in which time span

for which company, i.e.:

.

"Tom" at "Mario's Apples" is the problem -

I cannot just simply take

proc means data=have noprint;
	output out=list (drop = _FREQ_ _TYPE_)
	min(beginning_date)	=	beg_date_manager
	max(ending_date)		=	end_date_manager;
	by
		company
		Manager;
run;

, because then it would appear as if "Tom" would have worked all time at "Mario's Apples".

If I had this nice ID-variable, I were able to include it in my by-statement and I was fine.

So if someone could please tell me how to create this very ID (because in general it would help me to know how to do it) or otherwise could provide me with a means of how to solve my problem, for the means of which I have thought of appears as rather clumsy to me ("there for sure is a much more sophisticated solution"), I would be very glad.

Yours sincerely,
Sinistrum

[RW9 · Accepted solution]

Post test data in the form of a datastep in the body of the post, I don't download files, or type in test data.  So this is just a rough outline.  You should just be able to increment at each new manager:

data want;
  set have;
  by company manager notsorted;
  retain id;
  if first.company then id=0;
  if first.manager then id=sum(id,1);
run;

[Sinistrum]

Thank you very much indeed -

it once again is truly breathtaking, how fast I receive responses.