topic Re: Selecting multiple row data by id simultaneously in SAS Programming

Selecting multiple row data by id simultaneously

Wolves — Thu, 04 May 2017 15:58:28 GMT

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?

Re: Selecting multiple row data by id simultaneously

PeterClemmensen — Mon, 01 May 2017 14:03:59 GMT

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

Re: Selecting multiple row data by id simultaneously

ballardw — Mon, 01 May 2017 14:12:18 GMT

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.

Re: Selecting multiple row data by id simultaneously

thomp7050 — Mon, 01 May 2017 14:46:24 GMT

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;