Could you please help me to resolve the following syntax error? I'm trying to do this under expression in SAS DI   case when KNKBTY = '1' then TBFR10.AGI 5 when KNKBTY = '2' then QISFR10.AGI 6 when QISFR10.AGI=. then TBFR10.AGI 7 end 8 end ___ 22 76 ERROR 22-322: Syntax error, expecting one of the following: ;, !, !!, &, *, **, +, -, /, <, <=, <>, =, >, >=, ?, AND, BETWEEN, CONTAINS, EQ, EQT, EXCEPT, GE, GET, GROUP, GT, GTT, HAVING, IN, INTERSECT, IS, LE, LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, ORDER, OUTER, UNION, ^, ^=, |, ||, ~, ~=. ERROR 76-322: Syntax error, statement will be ignored. 9 end; ... View more

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; ... View more

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, 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 from connection to db2( 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 ); /**** 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.*, y.name 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 ); quit; ... View more

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:       ... View more

Could you please guide me understand what each code does? can I understand that technically both the codes are selecting the variables from DB2 table?   select distinct createdbylogonuserid, left(trim(lastname))||' '||left(trim(firstname)) as creator, creator_duser from connection to db2( select b.identifier as ...... Another one is,   select * from connection to db2( select x.policyinstanceid , x.customerID, y.value as client_segment   ... View more