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.
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;
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;
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!
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;
Perfect!. Thank you.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.