Selecting multiple row data by id simultaneously

[Wolves]

Hello all,

I have about a million rows of data which I want to do some analysis on. The data contains a list of all employees. Each project the employees have worked on has had an opp_IDs attached to it. Each opp_ID may have multiple rows showing what has happened to try to get the project completed and the progress completed. The data thus has 3 levels of nesting.

I am trying to pull just the top 1000 projects by opp_Id and all of their subsequent sub data. Thus there should be many more rows than just the initial 1000.

Here is the gist.

Employee Name |  Opp_ID  |   MONTH  |  Progress

John     |    1234567   |     1    |    20

John     |    1234567   |     2   |    40

John     |    1234567   |     3    |   60

John     |     324783   |    1     |   80

John     |     324783   |    2     |   100

Sol     |    1234567   |     4    |    100

Sol     |    489932   |    1    |    20

.

.

.

My desired sorting is simply

proc sort data=have;

by Opp_ID month

run;

But I only want the top 1000 opp_IDs with all of the subsequent information (Ie. month 1, 2, 3, 4)

Anyone have an idea?

[PeterClemmensen]

Can you provide some sample of your data and what you want the outcome to look like?

[ballardw]

Do you have the "projects" of interest in a data set?

Or are you requesting to analyze the data to find the "top 1000" projects? If this is what you are attempting you will have to define what "top" means and what variables in your data are needed to determine the status.

[thomp7050 · Accepted solution]

Theoretically speaking, it may be best to create a table of the top 1000 opportunity IDs first, and then select all records from your original table that have those ids.  As such:

data have;
input emp_name $ opp_id month subj_prob;
datalines;
John 1234567 1 20
John 1234567 2 40
John 1234567 3 60
John 324783 1 80
John 324783 2 100
Sol 1234567 4 100
Sol 489932 1 20
;
run;

PROC SQL;
CREATE TABLE TOP1K AS
SELECT OPP_ID, COUNT(*) AS TOTAL FROM HAVE GROUP BY OPP_ID;
QUIT;

PROC SQL OUTOBS=1000;
CREATE TABLE TOP1K AS
SELECT * FROM TOP1K;
QUIT;

PROC SQL;
CREATE TABLE ALLIN1K AS
SELECT * FROM HAVE
INNER JOIN TOP1K ON TOP1K.OPP_ID = HAVE.OPP_ID;
QUIT;