topic Run one proc sql or multiple proc sqls in SAS Programming

Run one proc sql or multiple proc sqls

Ronein — Tue, 20 May 2025 11:46:23 GMT

Hello,

What do you think is more efficient (run time)-

Way1-- Run 2 merge in one proc sql

WAY2- Run 1 merge in one proc sql and then one merge in second proc sql

/***WAY1***/
/***WAY1***/
/***WAY1***/
proc sql;
create table WANT as
select  a.*,
		b.Branch_Cust_IP,
		b.Branch_Cust_End_Date    as _Lak_date_Close_ format=ddmmyy10.,
		b.Branch_Cust_Start_Date  as Lak_date_Open  format=ddmmyy10.,
		c.Numerator as lakoach 
from Lak_Heshbon_list as a
inner join teradata.VBM374_USED_BRANCH_CUSTOMER  as b
ON a.LAK_ID=b.Branch_Cust_Nbr and a.snif=b.Branch_Nbr
left join teradata.V01X26_MARKETING_REQ_NUMERATOR  as c
ON b.Branch_Cust_IP=c.Inv_Party_Id 
 ;
quit;


/***WAY2***/
/***WAY2***/
/***WAY2***/
proc sql;
create table ttt_a as
select  a.*,
		b.Branch_Cust_IP,
		b.Branch_Cust_End_Date    as _Lak_date_Close_ format=ddmmyy10.,
		b.Branch_Cust_Start_Date  as Lak_date_Open  format=ddmmyy10.,
		c.Numerator as lakoach
from Lak_Heshbon_list as a
inner join teradata.VBM374_USED_BRANCH_CUSTOMER  as b
ON a.LAK_ID=b.Branch_Cust_Nbr and a.snif=b.Branch_Nbr
 ;
quit;

proc sql;
create table WANT as
select a.*,b.Numerator as lakoach
from   ttt_a as a
left join teradata.V01X26_MARKETING_REQ_NUMERATOR  as b
ON a.Branch_Cust_IP=b.Inv_Party_Id 
;
quit;

Re: Run one proc sql or multiple proc sqls

PaigeMiller — Tue, 20 May 2025 11:57:06 GMT

Does not the answer from @Kurt_Bremser to use Maxim 4 apply here?

Re: Run one proc sql or multiple proc sqls

Kurt_Bremser — Tue, 20 May 2025 13:48:27 GMT

Rather than tinkering around with PROC SQL, you should try DATA step methods, e.g. hash objects if data can be fit into memory.

Re: Run one proc sql or multiple proc sqls

SASKiwi — Wed, 21 May 2025 00:30:29 GMT

Looks like you are joining a SAS table with a Teradata table. If that is the case then neither SQL example is likely to be very efficient as the whole Teradata table has to be extracted to the SAS server before it can do the join with the SAS table.

Re: Run one proc sql or multiple proc sqls

Ksharp — Wed, 21 May 2025 01:43:06 GMT

For the sake of security, I would prefer to way2 .