Appreciate if someone of you help me understand to put the following in where clause in Join transformation?
WHERE (substr(t1.POLICY_ID,1,1)) = '9' AND t1.DELETED_IND = 0 AND t1.POLICY_VERSION = 1 AND
(datepart(t1.CREATED_DTTM)) >= today()-17
and
(datepart(t1.CREATED_DTTM)) <= today()-4;
you can choose whether the Join transformation should use ON or just WHERE
Select the Join in the outline and go down to join properties, this is where you can set implicit = no, so that you use the ON for the join and WHERE for everything else
What's the problem? Each line will be one condition so just start adding these conditions line by line. First line will be:
substr(t1.POLICY_ID,1,1)) = '9'
You should end up with 5 lines as below:
substr(t1.POLICY_ID,1,1) = '9' AND t1.DELETED_IND = 0 AND t1.POLICY_VERSION = 1 AND datepart(t1.CREATED_DTTM) >= today()-17 and datepart(t1.CREATED_DTTM) <= today()-4
Just click into column Operand, select in the drop down Choose column(s) and select your column. This will populate the field with
t1.POLICY_ID
Then click again into the field and edit the entry by wrapping your substr() function around the column.
From a DIS tool usage perspective: Yes, the panel works the same for the Where and the On clause.
From a coding perspective: I can't tell you if your logic needs to go into the Where or the On clause without knowing the details of what have and what you want.
With the following code, could you please clarify whether I can delete the default condition in where clause and add the condition which I mentioned before?
CREATE TABLE WORK.QUERY_FOR_QISDM_COVERAGES_C_000B AS
SELECT DISTINCT t1.CREATED_DTTM,
t11.POLICY_ID,
t11.POLICY_START_DT,
t11.POLICY_SUSPENSION_START_DT,
t11.POLICY_END_DT,
t11.POLICY_STATUS_CD
FROM OEXTCOM.COVERAGES t1
INNER JOIN OEXTCOM.DIM_POLICY t11 ON (t1.POLICY_SK = t11.POLICY_SK)
WHERE (substr(t1.POLICY_ID,1,1)) = '9' AND t1.DELETED_IND = 0 AND t1.POLICY_VERSION = 1 AND
(datepart(t1.CREATED_DTTM)) >= today()-17
and
you can choose whether the Join transformation should use ON or just WHERE
Select the Join in the outline and go down to join properties, this is where you can set implicit = no, so that you use the ON for the join and WHERE for everything else
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.