Help using Base SAS procedures

Creating a new sort variable by different groupings

Accepted Solution Solved
Reply
Contributor
Posts: 54
Accepted Solution

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. Smiley Sad

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 Smiley Wink

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

Attachment

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

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;

View solution in original post


All Replies
PROC Star
Posts: 7,492

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: 7,492

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.

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

Discussion stats
  • 5 replies
  • 141 views
  • 0 likes
  • 3 in conversation