BookmarkSubscribeRSS Feed
Babloo
Rhodochrosite | Level 12

Appreciate if someone of you guide me to combine the following SQL into one single step via left join. session.incomm table is common for all the below SQL.

 

/* Inbox for communication */
create table incomm4 as
select distinct b.identifier as inboxforcommunicationID, b.name as afdeling
from session.incomm a left join qis.rpinboxforcommunication b
on a.inboxforcommunicationID=b.identifier
where b.identifier in ('00000000001','10000000000','10000000001','10000000003')
);
/* createur */
create table incomm5 as
select distinct createdbylogonuserid, left(trim(lastname))||' '||left(trim(firstname)) as creator, creator_duser
from connection to db2(
select 	b.identifier as createdbylogonuserid,
		b.externalidentifier as creator_duser,	
		b.lastname,b.firstname
from session.incomm a  left join qis.rplogonuser b
on a.createdbylogonuserid=b.identifier
);
/**** INFO CLIENT ***/
create table incomm6 as
select x.policyinstanceid , x.customerID, y.value as client_segment from
(select b.identifier as policyinstanceid , b.customerID
from session.incomm a left join qis.rppolicyinstance b
on a.policyinstanceid=b.identifier) as x left join qis.rpcustomerfield y
on  x.customerid=y.customerid where y.externalidentifier='customerSubsegmentGE'
);
/**** INFO Policy ***/
proc sql;
create table incomm8 as
select distinct x.*, y.name as product from
(select b.identifier as policyid   , b.productid
	from session.incomm a left join qis.rppolicy b
	on a.policyid=b.identifier) as x left join qis.rpproduct y
	on x.productid=y.identifier
);

quit;
1 REPLY 1
LinusH
Tourmaline | Level 20
Since none of the queries is reusing any result from any of the other queries I can't see how they can be combined. Unless that there's succeeding steps that you don't share.

And right now the code is totally in DB2 SQL which would not make this a SAS question. On the other hand, I can't see any DB2 specifics in the code, so it could easily be transformed to implicit SQL, which IMO is easier to read and maintain.
Data never sleeps

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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