Combine data step and proc sql

[Babloo]

Could  someone of you guide me to combine the below data step and proc sql in one step?

proc sql;
create table todocomm as
select 	a.ExternalIdentifier as commnr,
		a.identifier as communicationID,
   		a.communicationtypeid,
		a.communicationdescriptionid,
		a.inboxforcommunicationID,
   		a.isincoming as incoming,
		a.policyid,
   		a.policyinstanceid,
   		a.tscommunicationcreated,
   		a.datetobecompleted,
   		a.createdbylogonuserid,
   		b.externalidentifier as polisnr
   from db2qiso.rpcommunication a, db2qiso.rppolicy b
   where a.policyid=b.identifier;
quit;

proc sql;
	create table todocomm2 as
	select a.*, b.tsevent, b.communicationstatusid
	from todocomm a, db2qiso.rpcommunicationevent b
	where a.communicationID=b.communicationid
	and int(sum(tsevent,0)/86400) < &einddat;
quit;
proc sort data=todocomm2 out=todocomm2; by polisnr commnr tsevent; run;

data todocomm3;
  set todocomm2;
  by polisnr commnr tsevent;
  if last.commnr; 
run;

data todocomm4 (drop=tsevent communicationstatusid);
  set todocomm3;
  if communicationstatusid not in ('00000000003' '00000000004');
run;

[ChrisNZ]

Unless performance is an issue, I would keep 2 steps.

This should be equivalent to what you have:

proc sql;
create table todocomm as
select a.EXTERNALIDENTIFIER        as COMMNR  
      ,a.IDENTIFIER                as COMMUNICATIONID  
      ,a.COMMUNICATIONTYPEID  
      ,a.COMMUNICATIONDESCRIPTIONID  
      ,a.INBOXFORCOMMUNICATIONID  
      ,a.ISINCOMING                as INCOMING  
      ,a.POLICYID  
      ,a.POLICYINSTANCEID  
      ,a.TSCOMMUNICATIONCREATED  
      ,a.DATETOBECOMPLETED  
      ,a.CREATEDBYLOGONUSERID  
      ,b.EXTERNALIDENTIFIER        as POLISNR  
      ,c.TSEVENT
      ,c.COMMUNICATIONSTATUSID
   from db2qiso.RPCOMMUNICATION      a
          inner join
        db2qiso.RPPOLICY             b
          on a.POLICYID = b.IDENTIFIER
          inner join
        db2qiso.RPCOMMUNICATIONEVENT c
          on  a.IDENTIFIER = c.COMMUNICATIONID
          and int(sum(c.TSEVENT,0)/86400) < &einddat.
  order by POLISNR, COMMNR, TSEVENT; 
quit;

data TODOCOMM2;
  set TODOCOMM;
  by POLISNR COMMNR TSEVENT;
  if last.COMMNR; 
  if COMMUNICATIONSTATUSID not in ('00000000003' '00000000004');
run;

Notes:

1. If this query is passed to DB2, too much data might be sent to SAS as the clause int(sum(c.TSEVENT,0)/86400) < &einddat. might not be sent to DB2. Check this point by using option sastrace = ',,,d'

2. SQL has no easy to to code the last. operator, and the data step has no easy way to merge and sort by multiple variables. Hence my preference to keep 2 steps. 

[LaurieF]

The last. syntax is not (very easily) available in SQL, so I'd recommend keeping it as two steps:

proc sql;
create table todocomm as
select 	a.ExternalIdentifier as commnr,
		a.identifier as communicationID,
   		a.communicationtypeid,
		a.communicationdescriptionid,
		a.inboxforcommunicationID,
   		a.isincoming as incoming,
		a.policyid,
   		a.policyinstanceid,
   		a.tscommunicationcreated,
   		a.datetobecompleted,
   		a.createdbylogonuserid,
   		b.externalidentifier as polisnr,
        c.tsevent, 
        c.communicationstatusid
   from db2qiso.rpcommunication as a
  inner join db2qiso.rppolicy as b
     on a.policyid=b.identifier
  inner join db2qiso.rpcommunicationevent as c
     on a.communicationID = c.communicationid
    and int(sum(c.tsevent, 0) / 86400) < &einddat
  order by polisnr,
           commnr,
           c.tsevent;
quit;

data todocomm3;
  set todocomm;
  by polisnr commnr;
  if last.commnr; 
  if communicationstatusid not in ('00000000003' '00000000004');
drop tsevent communicationstatusid;
run;

[Babloo]

Got it. How will you combine these two SQL now?

/* 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
(
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
);

[ChrisNZ]

1. This is a different question, start a new thread.

2. You can't combine these as shown. They don't depend on each other and just create 2 different tables with different columns.

[Babloo]

Those two SQL's has a common table session.incomm and it made me to believe to combine into one.