Selecting multiple row data by id simultaneously

Accepted Solution Solved
Reply
Regular Learner
Posts: 1
Accepted Solution

Selecting multiple row data by id simultaneously

[ Edited ]

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?


Accepted Solutions
Solution
‎05-01-2017 11:20 AM
Frequent Contributor
Posts: 93

Re: Selecting multiple row data by id simultaneously

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;

View solution in original post


All Replies
Valued Guide
Posts: 518

Re: Selecting multiple row data by id simultaneously

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

Grand Advisor
Posts: 10,223

Re: Selecting multiple row data by id simultaneously

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.

Solution
‎05-01-2017 11:20 AM
Frequent Contributor
Posts: 93

Re: Selecting multiple row data by id simultaneously

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;
☑ This topic is SOLVED.

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

Discussion stats
  • 3 replies
  • 153 views
  • 1 like
  • 4 in conversation