BookmarkSubscribeRSS Feed
sunilreddy
Fluorite | Level 6

Hi,

I am using below sql query to extract the results for my requirement. I am able to get the reslts, but its taking 2 mins time for 10 input records (table: Ext_cus) also.

Could you please some one help me extract the same results in any efficient way.

Req: if macro variable '&reg_cnt is ne 0 then i have to extarct all the records from t1. else i have to extract the records by join with reg_list

%macro ext_final;
proc sql;
   create table work.ext_final as
select * from Ext_cus t1

where
%if &reg_cnt ne 0 %then
      (t1.region_id ne ' ');
%else (t1.customer_group_cd in (select region_nm1 from reg_list where region_nm1 ne ' ')) or
      (t1.customer_id in (select region_nm2 from reg_list where region_nm2 ne ' '))   or
      (t1.region_id in (select region_nm3 from reg_list where region_nm3 ne ' ')) ;
;
quit;
%mend;
%ext_final;

5 REPLIES 5
Reeza
Super User

Try using a left join instead of a IN from the various tables.

sunilreddy
Fluorite | Level 6

I am using same table in Multiple IN

Doc_Duke
Rhodochrosite | Level 12

doesn't matter.  the left join is generally more efficient as it can take advantage of indesing and the IN cannot.

Reeza
Super User

exactly so won't the table have to be read multiple times with the different conditions?

Try the left join and let me know how much time that takes.

Doc_Duke
Rhodochrosite | Level 12

Do you have missing data in T1?  The NE operator is very inefficient, so avoid it if you can.  Even if you have some missing data, it might be more efficient to do the selects without the WHERE clauses and then delete the missing data later.

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 5 replies
  • 767 views
  • 0 likes
  • 3 in conversation