topic Re: Efficient SQL in SAS Programming

Combine multiple SQL queries into a single step via JOINs

Babloo — Wed, 26 Sep 2018 12:43:51 GMT

Re: Efficient SQL

Kurt_Bremser — Tue, 25 Sep 2018 07:48:29 GMT

One create table can only create one dataset/table, so you can't put the 4 steps into one select.

You might be able to do everything in one data step, if it is feasible to use formats created from the "right" datasets.

Re: Efficient SQL

RW9 — Tue, 25 Sep 2018 08:00:31 GMT

In what sense? The four SQL statements create four datasets, one SQL statement cannot create more than one table. A datastep might be able to. Do you mean join all those tables into one big dataset? That's possible, then its a matter of combining the selects so each variable is unique, and then adding the correct joins.

Re: Efficient SQL

Babloo — Tue, 25 Sep 2018 08:40:31 GMT

Yes, I want to combine all the datasets into one big data set.

Re: Efficient SQL

Kurt_Bremser — Tue, 25 Sep 2018 09:06:04 GMT

@Babloo wrote:
Yes, I want to combine all the datasets into one big data set.

Create formats for the lookups, and use those in one data step.

Re: Efficient SQL

RW9 — Tue, 25 Sep 2018 09:07:50 GMT

Ok, then its a matter of looking at each select statement - make sure you have the variables you want in the output dataset, and they are unique. Then look at each join and see how it relates to each other - this is why most would have some sort of plan of joins.

From:

from session.incomm a left join qis.rpcommunicationdescription b
on a.communicationdescriptionid=b.identifier
from session.incomm a  left join qis.rpcommunicationtype b
on a.communicationtypeid=b.identifier
from session.incomm a left join qis.rpinboxforcommunication b
on a.inboxforcommunicationID=b.identifier
where b.identifier in ('00000000001','10000000000','10000000001','10000000003')

from session.incomm a  left join qis.rplogonuser b
on a.createdbylogonuserid=b.identifier

What can we tell from these, well session.incomm appears to be the base table for all the joins, so this should appear in the from first, then all other datasets will left join to that. There are then four other datasets in the list, one with a restriction, so a shell of our from would look something like:

from       session.incomm a
left join  qis.rpcommunicationdescription b
on         a.communicationdescriptionid=b.identifier
left join  qis.rpcommunicationtype c
on         a.communicationtypeid=c.identifier
left join  (select distinct identifier as inboxforcommunicationid, name as afdeling from qis.rpinboxforcommunication where b.identifier in ('00000000001','10000000000','10000000001','10000000003')) d
on         a.inboxforcommunicationID=d.identifier
left join  qis.rplogonuser e
on         a.createdbylogonuserid=e.identifier

Select should be a case of selecting the variables from each alias dependant on what you want in the output.

That should get you started, as I do not have any test data in the form of a datastep or what you want out, I can only provide untested guidance.

Re: Efficient SQL

Babloo — Tue, 25 Sep 2018 09:28:37 GMT

That makes sense. How would you join the following SQL along with the code which posted earlier/above. Since there are two SQLs, I'm not sure to combine the below query with the master SQL.

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'
);

Re: Efficient SQL

RW9 — Tue, 25 Sep 2018 09:57:16 GMT

How does that relate to

session.incomm

If its not related, then how is it related to one of the other tables? This is why you really need some sort of SQL plan to describe each table and how they link to other tables, there are lots of examples out there:
https://www.google.ch/search?q=example+visual+sql+plan&rlz=1C1GGRV_enGB751GB751&tbm=isch&source=iu&ictx=1&fir=0fYJil5fFueRxM%253A%252CgsV8SSOXM-_v9M%252C_&usg=AI4_-kQv8lH_bYEN0BgNe371Cir8cT9J2g&sa=X&ved=2ahUKEwjQiY3i8dXdAhWPblAKHZeXDWsQ9QEwAXoECAMQBg#imgrc=ekzxoSo1Diuu8M:

Just draw out each of your tables, highlighting key variables which link each table. Once you have that the coding is very simple, just deciding on filters, and type of join.

Re: Efficient SQL

Babloo — Tue, 25 Sep 2018 11:03:51 GMT

session.incomm is created from seperate SQL and it is the table where it is common for all the tables per my previous post. Now could you please guide me how to incorporate the following SQL with your code?

create table incomm5 as
select distinct createdbylogonuserid, left(trim(lastname))||' '||left(trim(firstname)) as creator, creator_duser
from(
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);

Re: Efficient SQL

RW9 — Tue, 25 Sep 2018 11:07:16 GMT

This is something you, the person who has the data, knows the data, know the plan, to do. Either that or hire a contractor to come in and do it for you. I cannot code things with no information or data or knowledge about how things link together.

Re: Efficient SQL

Ksharp — Tue, 25 Sep 2018 12:49:51 GMT

make INDEX variables is able to get you faster.

Combine SQL

Babloo — Tue, 25 Sep 2018 14:04:44 GMT

Could you please help me understand to combine the following SQL with the master SQL?

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'
);

Master SQL,

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;