BookmarkSubscribeRSS Feed
Barite | Level 11

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, 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,	
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.*, 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

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



Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

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.

Get the $99 certification deal.jpg



Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 1 reply
  • 2 in conversation