Creating a new sort variable by different groupings

[Mishka1]

Hello. I have deleted my last post. I think now I know exactly what I need now, but I haven’t been able to do it.

We have projects that are prioritized in groups and assigned to workers.

We have A, B and C priority groups. A is the highest priority followed by B followed by C.

Within each priority groups are sub-priorities: All of the ‘A’s get a numbered sub-priority from 1, being the highest priority to the greatest number being the lowest priority. Illustrated below:

(The cell highlighting is not mine on any of the pasted tables below)

Worker	Project	Priority	SubPriority
Abby	Pj1	A	1
Abby	Pj2	A	2
Bill	Pj3	A	3
Cindy	Pj4	B	1
Bill	Pj5	B	2
Cindy	Pj6	B	3
Abby	Pj7	B	4
Abby	Pj8	B	5
Abby	Pj9	B	6
Cindy	Pj10	C	1
Cindy	Pj11	C	2

The report I need to create will show what everyone’s working on by priority group.

When I create the report with the data set above, I get each Project on its own row because I’m sorting by sub priority.

What I need to do is create a “Personal Priority” variable that looks like below:

Worker	Project	Priority	SubPriority	PersonalPriority
Abby	Pj1	A	1	1
Abby	Pj2	A	2	2
Abby	Pj7	B	4	1
Abby	Pj8	B	5	2
Abby	Pj9	B	6	3
Bill	Pj3	A	3	1
Bill	Pj5	B	2	1
Cindy	Pj4	B	1	1
Cindy	Pj6	B	3	2
Cindy	Pj10	C	1	1
Cindy	Pj11	C	2	2

The PersonalPriority variable shows what an individual worker’s priorities are for each A,B,C group disregarding projects not assigned to the worker.

I need that variable so I can use it in a proc report to get something like this:

		Worker
Priority	PersonalPriority	Abby	Bill	Cindy
A	1	Pj1	Pj3
	2	Pj2
A Total		2	1
B	1	Pj7	Pj5	Pj4
	2	Pj8	Pj6
	3	Pj9
B Total		3	1	2
C	1			Pj10
	2		Pj11
C Total				2

My next question, if I don’t figure it out first, will be how to get the project names to appear as shown instead of an aggregate sum or count. One at a time

I've attached a text file with the test data for your convenience. Thank you.

[art297]

If I correctly understand what you are trying to do, the sort part is easy.  Does the following accomplish what you need?

data have;

  input Worker $ Project $ Priority $ SubPriority;

  cards;

Abby          Pj1          A          1

Abby          Pj2          A          2

Bill          Pj3          A          3

Cindy          Pj4          B          1

Bill          Pj5          B          2

Cindy          Pj6          B          3

Abby          Pj7          B          4

Abby          Pj8          B          5

Abby          Pj9          B          6

Cindy          Pj10          C          1

Cindy          Pj11          C          2

;

proc sort data=have out=want;

  by Worker Priority SubPriority;

run;

data want;

  set want;

  by Worker Priority;

  if first.Worker then PersonalPriority=1;

  else PersonalPriority+1;

run;

[Mishka1]

This is very close. I need the numbering to start over for each priority group an individual has.

Your code gives me the PersonalPriority below for Abby. I need the Needed Priority below:

Worker	Priority	Personal Priority	Needed Priority
Abby	A	1	1
Abby	A	2	2
Abby	B	3	1
Abby	B	4	2
Abby	B	5	3

I tried adding first.priority, which makes sense to me but didn't work for some reason.

data want;

  set want;

  by Worker Priority;

  if first.Worker and first.priority then PersonalPriority=1;

  else PersonalPriority+1;

run;

Any ideas why adding first.priority wouldn't work?

Thanks!

[art297 · Accepted solution]

You are trying to calculate two different fields.  I think the following does what you want:

data want;

  set want;

  by Worker Priority;

  if first.Worker then PersonalPriority=1;

  else PersonalPriority+1;

  if first.Priority then NeededPriority=1;

  else NeededPriority+1;

run;

[Mishka1]

Perfect!. Thank you.

[MikeZdeb]

hi ... here's another idea for a within-group counter (using Art's data set) ...

proc sort data=have;

  by Worker Priority SubPriority;

run;

data want;

  set have;

  by Worker Priority;

  PersonalPriority + (-first.worker*personalpriority) + 1;

  NeededPriority + (-first.priority*neededpriority) + 1;

run;