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?
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;
Can you provide some sample of your data and what you want the outcome to look like?
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.
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.