DATA Step, Macro, Functions and more

Anything wrong with this simple proc sql code?

Accepted Solution Solved
Reply
Super Contributor
Posts: 312
Accepted Solution

Anything wrong with this simple proc sql code?

proc sql;
create table new_sample as
    select a.*
	    from total_sample as a,
		     selected_3000
	    where a.ID in (select ID from selected_3000 );
quit;

 

Total_sample has 79 million data rows, from 79,000 unique IDs. File size 90G.

selected_3000 only has 3000 rows with 3000 unique IDs.

 

Now I want to select those whose IDs are in selected_3000 from the total_sample, using the above proc sql code.

However, it generated a huge file >200G and I had to terminate the procedure. I checked the output huge file and

found the same row was repeated so many times.

 

What could be the problem in this  proc sql code?


Accepted Solutions
Solution
‎09-01-2016 12:26 PM
Occasional Contributor
Posts: 8

Re: Anything wrong with this simple proc sql code?

[ Edited ]

 

Try this:

proc sql;
create table new_sample as
    select *
	    from total_sample
	    where a.ID in (select ID from selected_3000 );
quit;

You don't need to have selected_3000 in the main query and also in the subquery. Since you had it in the main query, it was creating a cartesian product. It was returning 3000 times as many rows as you needed.

 

View solution in original post


All Replies
Solution
‎09-01-2016 12:26 PM
Occasional Contributor
Posts: 8

Re: Anything wrong with this simple proc sql code?

[ Edited ]

 

Try this:

proc sql;
create table new_sample as
    select *
	    from total_sample
	    where a.ID in (select ID from selected_3000 );
quit;

You don't need to have selected_3000 in the main query and also in the subquery. Since you had it in the main query, it was creating a cartesian product. It was returning 3000 times as many rows as you needed.

 

☑ This topic is SOLVED.

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

Discussion stats
  • 1 reply
  • 194 views
  • 0 likes
  • 2 in conversation