MAX function can be used in calculated item. Can you please provide the expression of your calculated item?
You did mention that vendor notified that it is not possible, is it SAS Tech Support. If so, it might be that the expression you had specified is not valid. It would be great if you can provide the expression so I can assess what is going wrong with it.
/Vijay
MAX function can be used in calculated item. Can you please provide the expression of your calculated item?
You did mention that vendor notified that it is not possible, is it SAS Tech Support. If so, it might be that the expression you had specified is not valid. It would be great if you can provide the expression so I can assess what is going wrong with it.
/Vijay
Can you please check the subject level at which you had created the calculated item? When you are trying to use the calculated item, please make sure you use the same subject level at which the calculated data item is created in the select node. If the subject is not matching then the calculated item will not be shown in the list.
/Vijay
Hi Vijay,
That's correct i have validated the subject level, which is same as what i have defined during calculated item creation. but it is not visible. Does this max function works alone ? i mean it does n't do the grouping and not allow the conditions as i shared. I mean no technical error but logically it does not allow to run ?
A similar query / Calculated Data Item works in my environment. However there are a couple things to consider:
1. Since you have an Aggregate in the Case Statement, when the calculated field is used in a Selection, the criteria will be evaluated in the Having portion of the generated SQL.
2. If you want to use it primarily in Selections at a customer level, You should also include the Campaign_Cd= as an additional Selection criteria to limit the CH rows being considered. And for Select Node that includes both Case stmt plus Campaign_CD = criteria, there is no need for Campaign_Cd in the Case.
Below is from my CI Core log showing the generated query:
Create table WORK.MA0 as
SELECT
( count(*) ) AS DII_1 LABEL=''
FROM
(
SELECT DISTINCT
table0.IndivID AS INDIVID LABEL='Individual ID'
FROM
CDM.CI_CONTACT_HISTORY table1 Inner join CDM.CI_CELL_PACKAGE table2 on table1.CELL_PACKAGE_SK = table2.CELL_PACKAGE_SK Inner join DataMart.INDIVIDUAL_SUMMARY table3 on table3.IndivID = table1.INDIVID Inner join DataMart.INDIVIDUAL table0 on table0.IndivID = table3.IndivID
WHERE
table2.CAMPAIGN_CD = 'CAMP92'
GROUP BY
1
HAVING
( case when table1.CELL_PACKAGE_SK = Max(table1.CELL_PACKAGE_SK) then table2.MARKETING_CELL_CD else 'NotMax' end ) = 'CELL227'
) table4 ;
quit;
When I run this same query in EG, the following Note is included in the Log:
NOTE: The query requires remerging summary statistics back with the original data.
This note means that the sql sent to the database does not include the Having. Instead, variables used in the Having clause are passed to the database as part of the Select's list of retrieved fields and then the resulting retrieved data is evaluated to determine the Having portion. Below shows the Log with options to show sql sent to dbms:
NOTE: The query requires remerging summary statistics back with the original data.
45 1907066468 tkvercn1 0 SQL (2)
OLEDB_6: Executed: on connection 14 46 1907066468 tkvercn1 0 SQL (2)
select table1."CELL_PACKAGE_SK", table0."IndivID" as "INDIVID", table2."CAMPAIGN_CD", table2."MARKETING_CELL_CD" from
CDM."CI_CONTACT_HISTORY" table1 inner join CDM."CI_CELL_PACKAGE" table2 on table1."CELL_PACKAGE_SK" = table2."CELL_PACKAGE_SK"
inner join DataMart."INDIVIDUAL_SUMMARY" table3 on table3."IndivID" = table1."INDIVID" inner join DataMart."INDIVIDUAL" table0 on
table0."IndivID" = table3."IndivID" where table2."CAMPAIGN_CD" = 'CAMP92' 47 1907066468 tkvercn1 0 SQL (2)
48 1907066468 tkvercn1 0 SQL (2)
ACCESS ENGINE: SQL statement was passed to the DBMS for fetching data. 49 1907066468 tkvercn1 0 SQL (2)
50 quit;
Your syntax is not supported by the SQL Standard. If I try to run the SQL in SQL Server, I get a message like:
Msg 8121, Level 16, State 1, Line 2
Column 'cidb.cdm.CI_CONTACT_HISTORY.CELL_PACKAGE_SK' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.
But SAS Proc SQL does support this syntax. But it requires some additional processing on the compute tier to accomplish it.
Even though SAS Proc SQL does support it, I not sure it will produce the results you want. Since the Max(Cell_Package_SK) is evaluated at the Customer level. The Group by is Customer, so the Max is the Max(Cell_Package_SK) for that customer. But the rows being considered for Max will be filtered by any Where clause, so assuming you include Campaign_CD = criteria, then every Customer in that Campaign will have a Max(Cell_Package_sk) value that matches 1 CH row. (possibly more if RTDM contacts are also available). So the count you receive from a Select node would be the same as just counting Customers that have Campaign_Cd = 'YourCampCd' and Offer_ID = 'Whatever Value you specified for Case='
If you're wanting to use the Calc Field on Export, then the Case statement moves from the Having clause to the Selection field list. In this case, your export settings, controlling the 1-Many relationship between Customer and contact History, will determine if you get 1 row per Customer or many rows per customer. Let's assume you want multiple rows per customer and then will look at rows with values that are not 'NA'. Each customer will have at least 1 row with non 'NA' value. You likely would want to include a Export Refinement to control the 1-Many relationship, like a Select Node with Campaign_Code='CAMP294'
In My system, the export returned:
"INDIVID","MaxCellSKCaseResults","CAMPAIGN_CD"
6,"CELL227","CAMP92"
6,"NA","CAMP92"
7,"CELL221","CAMP92"
7,"NA","CAMP92"
13,"CELL221","CAMP92"
13,"NA","CAMP92"
14,"CELL227","CAMP92"
14,"NA","CAMP92"
15,"CELL229","CAMP92"
15,"NA","CAMP92"
I'd like to understand what you're trying to accomplish with this Calculated field. What results you'd like to achieve. Seems more relevant for Export than Selection.
Possibly the example you're using with Max(Cell_Package_SK) is made up to test a concept against a table that has 1-Many relationship with the customer. And you want to do similar Calc Field against a Transaction Table.
Also, what release of MA are you using. And can you provide a tech support track number? Possibly it provides more info or I can weigh in on the Track relative to my similar use.
Hope this helps,
-Steve
Want to review SAS CI360? G2 is offering a gift card or charitable donation for each accepted review. Use this link to opt out of receiving anything of value for your review.
Listen to the Reimagine Marketing podcast
Assess your marketing efforts with a free tool
SAS Customer Intelligence Learning Subscription (login required)
Compatibility notice re: SAS 9.4M8 (TS1M8) or later
SAS' Peter Ansbacher shows you how to use the dashboard in SAS Customer Intelligence 360 for better results.
Find more tutorials on the SAS Users YouTube channel.
Want to review SAS CI360? G2 is offering a gift card or charitable donation for each accepted review. Use this link to opt out of receiving anything of value for your review.
Listen to the Reimagine Marketing podcast
Assess your marketing efforts with a free tool
SAS Customer Intelligence Learning Subscription (login required)
Compatibility notice re: SAS 9.4M8 (TS1M8) or later