BookmarkSubscribeRSS Feed
Babloo
Rhodochrosite | Level 12

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;
5 REPLIES 5
ChrisNZ
Tourmaline | Level 20

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
Barite | Level 11

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
Rhodochrosite | Level 12

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
Tourmaline | Level 20

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
Rhodochrosite | Level 12

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

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 5 replies
  • 1636 views
  • 0 likes
  • 3 in conversation