could someone of you guide me to combine the following SQL into the master SQL?
SQL to combine in the below master SQL (big one)
select x.policyinstanceid , x.customerID, y.value as client_segment from
(select f.identifier as policyinstanceid , f.customerID
from session.incomm a left join qis.rppolicyinstance f
on a.policyinstanceid=f.identifier) as x left join qis.rpcustomerfield y
on x.customerid=y.customerid where y.externalidentifier='customerSubsegmentGE'
proc sql noprint;
connect to &glb_db2.(datasrc=&glb_db2_src. Authdomain=DB2AuthODS connection=global);
create table incomm2 as
select * from connection to db2(
with temp1 as (select y.ExternalIdentifier as commnr,
y.communicationtypeid,
y.communicationdescriptionid,
y.inboxforcommunicationID,
y.isincoming as incoming,
y.policyid,
y.policyinstanceid,
y.tscommunicationcreated,
y.datetobecompleted,
y.createdbylogonuserid,
z.externalidentifier as polisnr
from qis.rpcommunication y, qis.rppolicy z
where y.policyid=z.identifier)
select b.name as documenttype, b.identifier as communicationdescriptionid,c.identifier as communicationtypeid ,c.externalidentifier,c.Name as commtype,
e.identifier as createdbylogonuserid,
e.externalidentifier as creator_duser,
e.lastname,e.firstname
from temp1 as a
left join qis.rpcommunicationdescription as b
on a.communicationdescriptionid=b.identifier
left join qis.rpcommunicationtype as c
on a.communicationtypeid=c.identifier
left join
(select distinct identifier as inboxforcommunicationid,name as afdeling from qis.rpinboxforcommunication where identifier in ('00000000001','10000000000','10000000001','10000000003')) as d
on a.inboxforcommunicationID=d.inboxforcommunicationid
left join qis.rplogonuser as e
on a.createdbylogonuserid=e.identifier
);
disconnect from db2;
quit;
Master SQL:
Hi @Babloo - as this is similar / related to some of your other recent posts, I decided to chime in.
It's clear to me from the code you shared that the data model is complex, and only a person with a good understanding of the relationships between these tables can provide a good answer. I don't think it's reasonable to expect a solution for the specific code question here.
However, if you have questions about good practices for joining tables, using subqueries in the process, or efficiencies that can be gained via a single query instead of multiple table pulls -- then those questions are fair game. Just make sure that the experts here don't need too much domain knowledge about your specific data to respond.
Also, please avoid posting multiple versions of the same/similar question. Since it's the same pool of experts reading/evaluating each topic, it's better if you keep a single topic together in a thread until it's solved.
Chris
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
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.
Ready to level-up your skills? Choose your own adventure.