topic Creating a new sort variable by different groupings in SAS Procedures

Creating a new sort variable by different groupings

Mishka1 — Wed, 18 Jan 2012 23:07:16 GMT

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.

Creating a new sort variable by different groupings

art297 — Wed, 18 Jan 2012 23:23:53 GMT

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;

Creating a new sort variable by different groupings

Mishka1 — Thu, 19 Jan 2012 16:47:53 GMT

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!

Creating a new sort variable by different groupings

art297 — Thu, 19 Jan 2012 16:59:40 GMT

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;

Creating a new sort variable by different groupings

Mishka1 — Thu, 19 Jan 2012 17:18:57 GMT

Perfect!. Thank you.

Re: Creating a new sort variable by different groupings

MikeZdeb — Thu, 19 Jan 2012 18:16:36 GMT

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;