BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Mishka1
Fluorite | Level 6

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.

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

5 REPLIES 5
art297
Opal | Level 21

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
Fluorite | Level 6

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
Opal | Level 21

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
Fluorite | Level 6

Perfect!. Thank you.

MikeZdeb
Rhodochrosite | Level 12

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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