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

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?

1 ACCEPTED SOLUTION

Accepted Solutions
thomp7050
Pyrite | Level 9

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

3 REPLIES 3
PeterClemmensen
Tourmaline | Level 20

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

ballardw
Super User

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
Pyrite | Level 9

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;

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 3 replies
  • 2131 views
  • 1 like
  • 4 in conversation