Need help with OLAP or If function in subquery

Analytics
Enthusiast

Need help with OLAP or If function in subquery

I have a pretty complex query that pulls the following "Insured IDs" from a table specified in the coalesce statement. The problem is that there are duplicate records with the exception of two letters in a string. I want to be able to only bring in the record with the "RT" In the string. I realize i might have to use a substring of sorts to accomplish this as well as a subquery? Perhaps an IF statement?

This is what the data looks like:

42078 00561070 610000000 06042129 Smith Joe 00561000000000000EE19470123F
42078 00561070 610000000 06042129 Smith Joe 00561000000000000RT19470123F

In the last column there is an "EE" and "RT" in the string, If the record has RT, i want to pull that, and not the record with the EE in the string of the last column.... I hope this makes sense....

The code is as follows

,Coalesce (idv.i_ird
,profl.sorce_cust_id
,kird.i_ird
,gird.i_ird <-------------------------------- This is the table with the "Duplicate" Records
,(rtrim(adpt.c_pol)||cast(adpt.c_typ_sys_idv as decimal format '999')||
cast(adpt.i_sys_idv as decimal format '99999')))
AS InsuredID

FROM pearl_p.tltc900_clm clm

The join for this table is here:

LEFT JOIN ltc_p.vltc_er_ird_mo gird
ON clm.c_pol = gird.i_pol AND
(LastName = gird.n_lst or FirstName = gird.n_fst or SSN = gird.i_ssn)AND
DOB = gird.d_bth

Any help is HUGELY appreciated!
Tags:

* if
* olap
* query
* then
10 REPLIES
Enthusiast

Re: Need help with OLAP or If function in subquery

Your requirement is not fully spelled out, but try using an OLAP and Case on your select from the gird table.
Something like:

LEFT JOIN
(Select i_pol
, i_ird
From ltc_p.vltc_er_ird_mo
Qualify Row_Number () Over (Partition By i_ird
Order by Case When Substring(i_ird From 17 for 2) = 'RT' Then 1 Else 2 END) = 1
) gird
ON clm.c_pol = gird.i_pol AND
(LastName = gird.n_lst or FirstName = gird.n_fst or SSN = gird.i_ssn)AND
DOB = gird.d_bth
Enthusiast

Re: Need help with OLAP or If function in subquery

Thank you for your reply! much appreciated. I'm running this code in SAS and getting the following error:

CLI Prepare error: Syntax Error type c_pol does not match defined type name.

Any ideas? I feel like this should work fine!
Enthusiast

Re: Need help with OLAP or If function in subquery

and username.gird.nlast does not exist?
Enthusiast

Re: Need help with OLAP or If function in subquery

Post the full SQL you are trying to run, together with show table output ffor all the tables involved.
Enthusiast

Re: Need help with OLAP or If function in subquery

options nosource nosource2 nosymbolgen nomprint;
%include 'J:\sas\password.sas';
options source source2 symbolgen mprint validvarname=v7 sasautos=(maclib macauto);

/*filename maclib 'J:\sas\mac';*/
filename macauto 'C:\Program Files\SAS\SAS 9.1\core\sasmacro';

%let SrcTable= ltc_claim_table;
%let SrcTable2= ltc_claim_loc;
%let teradata_odbc_id= tdprod;
%let logdb= pearl_p;
%let logtb= DiscontinuedLog;
%let sas_tbl= LTCClaim.DiscontinuedCovs;
%let targetdb= pearl_p;
%let targettb= LTC_DiscontinuedCoverages;

libname LTCClaim 'C:\Documents and Settings\diw07\My Documents\My SAS Files\LTCClaim';
libname teralib teradata user="&usrid" password=&pwdtd tdpid=tdprod logdb=&logdb;

/* Turn off excess displays to highlight Return Codes */
options nosource nosource2 nosymbolgen nomprint;

%put ================================================================================;
%put SAS System Error Code (SYSERR): &syserr;
%put Max SYSERR for AbendIt (MAXSYSERR): &MaxSYSERR;
%put ;
%put SAS System Return Code (SYSRC): &sysrc;
%put SQL Return Code (SQLRC): &sqlrc;
%put ;
%put SysDB Return Code (SYSDBRC): &sysdbrc;
%put SysDB Message (SYSDBMSG): &sysdbmsg;
%put ;
%put SQLX Return Code (SQLXRC): &sqlxrc;
%put SQLX Message (SQLXMSG): &sqlxmsg;
%put ;
%put SQL Rows Processed (SQLOBS): &sqlobs;
%put SQL Rows Processed (SQLXOBS): &sqlxobs;
%put ================================================================================;

/* %mAbendIt(12,ErrCode=&SYSERR,MaxErr=&MaxSYSERR); Requires a 2nd macro. Best for batch runs. */

options source source2 symbolgen mprint;

%mend mDispRC;
/* Step 1. Run the claim payment/location table to get the current location needed for the claim information below.******************/
PROC SQL;
CONNECT TO ODBC(dsn=&Teradata_ODBC_ID uid=&usrid pwd=&pwdtd);
CREATE TABLE LTCClaim.&SrcTable2. AS
SELECT *
FROM CONNECTION TO ODBC
(SELECT * FROM LTC_P.CLAIM_PMNT
ORDER by sorce_claim_id, pmnt_thru_dt
);
QUIT;
/* Step 2. Pull the claim and administration date into one table.******************************/
PROC SQL;
CONNECT TO ODBC(dsn=&Teradata_ODBC_ID uid=&usrid pwd=&pwdtd);
CREATE TABLE LTCClaim.&SrcTable. AS
SELECT *
FROM CONNECTION TO ODBC
(SELECT clm.i_sys_clm
,(CASE WHEN substr(clm.c_pol,1,1) NOT IN ('A','L','O')
AND i_pdt_gl in (12984, 22984, 363114, 363154)AND udf_isnumeric(clm.c_pol) = 0
THEN cast(cast(clm.c_pol as decimal(10,0) format '9999999999') as char(10))
WHEN substr(clm.c_pol,1,1) NOT IN ('A','L','O')
AND i_pdt_gl in (361114, 361154, 362114, 362154, 364114, 364154)
AND udf_isnumeric(clm.c_pol) = 0
THEN cast(cast(clm.c_pol as decimal(8,0) format '99999999') as char(8))
WHEN substr(clm.c_pol,1,1) NOT IN ('A','L','O')
AND i_pdt_gl IN (362114, 362154) AND udf_isnumeric(clm.c_pol) = 1
THEN CAST(CAST(substr(clm.c_pol, 1, CHARACTER_LENGTH(TRIM(TRAILING
from clm.c_pol))-2)as decimal format '99999999') as char(8))
WHEN substr(clm.c_pol,1,1) IN ('A','L','O') THEN clm.c_pol
ELSE 'xxxxxxxx'
END)AS i_pol
,gird.i_pol AS GrpPol
,clm.i_sys_idv
,Coalesce((profl.fclty_plan_dollr_amt*365/12)
,gprofile.a_bft_mo_nhm
,oprofile.a_bft_mo_nhm
,kcov.ks_ben_amt
,(adpt.a_bft_fst + adpt.a_bft_sec + adpt.a_bft_trd + adpt.a_bft_frt))
AS BenAmt
,clm.c_clm
,clm.c_usr_udt_lst
,clm.d_dby
,clm.d_tmn_clm
,clm.d_rop_clm
,clm.d_rcv_ntf
,clm.d_rcv_clm
,clm.c_sta_clm
,clm.d_sta_clm
,clm.c_tmn_clm_rsn
,clm.c_rop_clm_rsn
,clm.i_pdt_gl
,(CASE WHEN substr(clm.c_pol,1,4) = 'LAC5'
OR substr(clm.c_pol,1,4) = 'LNC5'
OR substr(clm.c_pol,1,4) = 'LAC6'
OR substr(clm.c_pol,1,4) = 'LNC6'
OR substr(clm.c_pol,1,4) = 'LAC7'
OR substr(clm.c_pol,1,4) = 'LNC7'
OR substr(clm.c_pol,1,4) = 'LAC8'
OR substr(clm.c_pol,1,4) = 'LNC8' THEN 'IN'
WHEN substr(clm.c_pol,1,4) in ('LAC1','LNC1') THEN 'PB'
WHEN substr(clm.c_pol,1,4) = 'LAC2'
OR substr(clm.c_pol,1,4) = 'LNC2' THEN 'AS'
WHEN clm.c_pol = 'LAC200001' THEN 'AF'
WHEN substr(clm.c_pol,1,3) = 'OAC' THEN 'KC'
WHEN substr(clm.c_pol,1,2) = 'AM' THEN 'AM'
WHEN i_pdt_gl = '12984' OR i_pdt_gl = '22984' THEN 'IN'
ELSE 'ER'
END) AS SubMarket
,coalesce((CASE WHEN clm.c_pol between 'LAC100001' AND 'LAC200027'
OR clm.c_pol = 'LNC200002' THEN 'UNUM America'
END)
,oprofile.c_co
,profl.uwrt_co_txt
,kpol.c_co
,gprofile.c_co)
AS Company
,Coalesce(cast(cast(cida.i_ssn as decimal (9,0) format '999999999') as char(9))
,cast(cast(gida.i_ssn as decimal (9,0) format '999999999') as char(9))
,idv.i_ssn)
AS SSN
,Coalesce(cida.d_bth
,gida.d_bth
,idv.d_bth, CAST( '1900-01-01' AS Date))
AS DOB
,Coalesce(cida.n_lst
,gida.n_lst
,idv.n_lst
,'')
AS LastName
,Coalesce(cida.n_fst
,gida.n_fst
,idv.n_fst
,'')
AS FirstName
,Coalesce (idv.i_ird
,profl.sorce_cust_id
,kird.i_ird
,gird.i_ird
,(rtrim(adpt.c_pol)||cast(adpt.c_typ_sys_idv as decimal format '999')||
cast(adpt.i_sys_idv as decimal format '99999')))
AS InsuredID

FROM pearl_p.tltc900_clm clm

/*GUY: This should be feeding from the discontinued tables in the warehouse: TLTC_DNU_COV....*/
/*ADEPT (Disontinued)Policy Table - From PEARL - needs to be updated to DW tables.*/
LEFT JOIN pearl_p.tltc906_GCV adpt
ON clm.c_pol = adpt.c_pol
AND clm.i_sys_idv = adpt.i_sys_idv

/* Join with the following table to pull the name, dob, AND ssn for the Merlin, Flex,
AND CCP policy holders.*************************************************************/
LEFT JOIN pearl_p.tltc917_cid cida
ON clm.i_sys_idv = cida.i_sys_idv
AND clm.c_typ_sys_idv = cida.c_typ_sys_idv

/* Join with the following table to pull the name, dob, AND ssn for the group
ADEPT policy holders.***************************************************************/
LEFT JOIN pearl_p.tltc907_gid gida
ON clm.i_sys_idv = gida.i_sys_idv
AND clm.c_typ_sys_idv = gida.c_typ_sys_idv

/* OPAL Insured Table: Used to join with PEARL data to pull the name, dob, AND
ssn for the ILTC OPAL policy holders************************************************/
LEFT JOIN ltc_p.vltc_idv_ird_mo idv /**/
ON clm.c_pol = idv.i_pol AND
LastName = idv.n_lst AND
FirstName = idv.n_fst AND
SSN = idv.i_ssn AND
DOB = idv.d_bth

/*Opal Profile Table*******************************************************************/
LEFT JOIN ltc_p.vltc_idv_profile_mo oprofile
ON idv.i_ird = oprofile.i_ird

/*Kansas City Insured Table************************************************************/
LEFT JOIN ltc_p.vltc_ks_ird_mo kird
ON clm.c_pol = kird.i_pol AND
LastName = kird.n_lst AND
FirstName = kird.n_fst /*AND
SSN = kird.i_ssn */AND
DOB = kird.d_bth

/*Kansas City Policy Table*************************************************************/
LEFT JOIN ltc_p.vltc_ks_pol_mo kpol
ON kird.i_pol = kpol.i_pol

/*Kansas City Coverage Table***********************************************************/
LEFT JOIN (SELECT Distinct(i_ird)
,sum(a_bft_mo_nhm) AS ks_ben_amt
FROM LTC_P.VLTC_KS_COV_MO
GROUP BY 1
WHERE c_sta_cov = 'TE') kcov
ON kird.i_ird = kcov.i_ird

/*CCP Profile Table*******************************************************************/
LEFT JOIN
(SELECT sorce_polcy_id
,sorce_cust_id
,txpay_id
,first_nm
,last_nm
,birth_dt
,uwrt_co_txt
,fclty_plan_dollr_amt
,fclty_benft_durtn_txt
,max(cov_eff_dt)AS MaxCovDt
FROM ltc_p.ltc_profl profl
GROUP BY 1,2,3,4,5,6,7,8,9
WHERE row_end_dt = '9999-12-31') profl
ON clm.c_pol = substr(profl.sorce_polcy_id, 3,8) AND
LastName = profl.last_nm AND
FirstName = profl.first_nm AND
SSN = txpay_id AND
DOB = profl.birth_dt

/*Group Insured Table****************************************************************FIX DUPLICATES HERE*/
LEFT JOIN
(Select i_pol
,i_ird
From ltc_p.vltc_er_ird_mo
Qualify Row_Number () Over (Partition By i_ird
Order by Case When Substring(i_ird From 17 for 2) = 'RT' Then 1 Else 2 END) = 1
) gird
ON clm.c_pol = gird.i_pol AND
(LastName = gird.n_lst or FirstName = gird.n_fst or SSN = gird.i_ssn) AND
DOB = gird.d_bth

/*Group Profile Table****************************************************************/
LEFT JOIN ltc_p.vltc_er_profile_mo gprofile
ON gird.i_ird = gprofile.i_ird

WHERE clm.c_pol NOT BETWEEN 'LAC000001' AND 'LAC000999'
AND clm.i_sys_clm not in(39323,39810,40395,40425,40167,40631,40633,
41149,41151,41427,42589,43563)
AND clm.c_sta_clm = 'AC'

ORDER BY i_sys_clm);
QUIT;
DATA LTCCLaim.GoodRecords;
SET LTCClaim.LTC_Claim_Table;
WHERE BenAmt ne . and i_pol ne 'xxxxxxxx' and InsuredID ne ' ' and Company ne ' ';
RUN;
PROC SORT DATA= LTCClaim.GoodRecords;
by i_sys_clm InsuredID;
RUN;
DATA LTCClaim.BadRecords;
SET LTCClaim.LTC_Claim_Table;
WHERE BenAmt = . or i_pol = 'xxxxxxxx' or InsuredID = ' ' or Company = ' ';
RUN;
PROC FREQ DATA = LTCClaim.BadRecords;
TABLES BenAmt i_pol InsuredID Company;
RUN;

PROC FREQ DATA=LTCClaim.LTC_Claim_Table;
TABLES i_sys_clm / out=LTCClaim.dup_no(WHERE=(Count >= 2));
RUN;
PROC SORT DATA = LTCClaim.dup_no;
by i_sys_clm;
RUN;
Enthusiast

Re: Need help with OLAP or If function in subquery

Here is the actual error when running the above code:

244 WHERE clm.c_pol NOT BETWEEN 'LAC000001' AND 'LAC000999'
245 AND clm.i_sys_clm not in(39323,39810,40395,40425,40167,40631,40633,
246 41149,41151,41427,42589,43563)
247 AND clm.c_sta_clm = 'AC'
248
249 ORDER BY i_sys_clm);
ERROR: CLI prepare error: [Teradata][ODBC Teradata Driver][Teradata Database] Column/Parameter
'DIW07.gird.n_lst' does not exist.
SQL statement: SELECT clm.i_sys_clm ,(CASE WHEN substr(clm.c_pol,1,1) NOT IN ('A','L','O') AND..........................................................................
Enthusiast

Re: Need help with OLAP or If function in subquery

You need to take all the needed columns from your Group Insured table in the OLAP part. So:

/*Group Insured Table************************************************* ***************FIX DUPLICATES HERE*/
LEFT JOIN
(Select i_pol
,i_ird
,n_lst
,n_fst
,i_ssn
From ltc_p.vltc_er_ird_mo
Qualify Row_Number () Over (Partition By i_ird
Order by Case When Substring(i_ird From 17 for 2) = 'RT' Then 1 Else 2 END) = 1
) gird
ON clm.c_pol = gird.i_pol AND
(LastName = gird.n_lst or FirstName = gird.n_fst or SSN = gird.i_ssn) AND
DOB = gird.d_bth

/*Group Profile Table************************************************* ***************/

There may be other needed columns elsewhere in your query! (If in doubt, you can do a Select * from this table, still using the Qualify. Lazy way out though.)
That should get you back to the c_pol is not defined type name error.
If you still have a problem with that, you need to play around to determine where the c_pol problem occurs. Comment out some of the statements involving the column until the code runs. Then you can narrow down why it is complaining. You will also need the table definitions for the tables.
Enthusiast

Re: Need help with OLAP or If function in subquery

Okay

I got it working with

Select i_pol
,i_ird
,n_lst
,n_fst
,i_ssn
,d_bth
From ltc_p.vltc_er_ird_mo gird
Qualify Row_Number () over (partition by i_pol
order by case when i_ird like 'EE%' then 0 else 1 end) = 1

I think this did the trick, but i will definitely get back to you - THANK YOU SO MUCH!!!! you help is VERY much appreciated!

However, I'm not seeing an
Enthusiast

Re: Need help with OLAP or If function in subquery

Looks like it's doing the trick!

My next question is, Say there is now a CH and i want to take EE over that? While still taking RT's over EEs, etc...

Or an RT but want to take CH over it and ETC...., Just new Cases?