Solved
Contributor
Posts: 54

# Creating a new sort variable by different groupings

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.

Accepted Solutions
Solution
‎01-19-2012 11:59 AM
PROC Star
Posts: 8,163

## Creating a new sort variable by different groupings

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;

All Replies
PROC Star
Posts: 8,163

## Creating a new sort variable by different groupings

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;

Contributor
Posts: 54

## Creating a new sort variable by different groupings

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!

Solution
‎01-19-2012 11:59 AM
PROC Star
Posts: 8,163

## Creating a new sort variable by different groupings

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;

Contributor
Posts: 54

## Creating a new sort variable by different groupings

Perfect!. Thank you.

Valued Guide
Posts: 765

## Re: Creating a new sort variable by different groupings

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;

🔒 This topic is solved and locked.