Help using Base SAS procedures

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

Accepted Solution Solved
Reply
Contributor
Posts: 42
Accepted Solution

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

[ Edited ]

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

Have.PNG

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

Want.PNG

 

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.:

List.PNG.

 

"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

Attachment

Accepted Solutions
Solution
‎02-07-2017 04:30 AM
Super User
Super User
Posts: 7,942

Re: Creation of

Posted in reply to Sinistrum

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;

 

View solution in original post


All Replies
Solution
‎02-07-2017 04:30 AM
Super User
Super User
Posts: 7,942

Re: Creation of

Posted in reply to Sinistrum

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;

 

Contributor
Posts: 42

Re: Creation of

Thank you very much indeed -

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

 

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 154 views
  • 1 like
  • 2 in conversation