I am a new user and I have created a number of new computed columns through the Query Builder. When I create a new computed column based on an existing computed column, the full text of the initial computed column is brought in (rather than a reference to the name of the column). I had since a reason to change one of the initial calculations, but I then found that the other columns are not automatically updated as it keeps the initial text. Is there a way to reference the cell so that further changes can be picked up throughout the model?
Thanks a lot, Anna
Hi Reeza,
Thank you for responding.
When I created the first computed column (called Provisioning) then it included the following code
(CASE
WHEN (IFC(t2.Sec_Classification_SubGroup="Securitisation - External", "Yes", "No")) = "Yes" THEN "No"
WHEN t2.Mortgage_Insurance = "Yes" THEN "No"
WHEN (ifc(t2.Account_Security_CD in ("1","2","5"),"Yes","No")) = "Yes" AND t2.Mortgage_Insurance = "Yes" THEN "No"
WHEN (ifc(t2.Account_Security_CD in ("1","2","5"),"Yes","No")) = "Yes" AND t2.Mortgage_Insurance = "No" AND (IFC (t2.MAX_of_ValuationDate > t7.Logical_Date_less_365,"Yes","No")) = "Yes" AND (ifc(t2.LVR>80,"Yes","No")) = "No" THEN "No"
WHEN (ifc(t2.Account_Security_CD in ("12"),"Yes","No")) = "Yes" AND t2.Mortgage_Insurance = "Yes" THEN "No"
WHEN (ifc(t2.Account_Security_CD in ("12"),"Yes","No")) = "Yes" AND (IFC (t2.MAX_of_ValuationDate > t7.Logical_Date_less_365,"Yes","No")) = "Yes" AND t2.Mortgage_Insurance = "No" AND (ifc(t2.LVR>80,"Yes","No")) = "No" THEN "No"
ELSE "Yes"
When I then want to use this Yes/No field in other calculated columns then it brings in the full code again, not the name of the computed column. For example.. (
CASE
WHEN (CASE
WHEN (IFC(t2.Sec_Classification_SubGroup="Securitisation - External", "Yes", "No")) = "Yes" THEN "No"
WHEN t2.Mortgage_Insurance = "Yes" THEN "No"
WHEN (ifc(t2.Account_Security_CD in ("1","2","5"),"Yes","No")) = "Yes" AND t2.Mortgage_Insurance = "Yes" THEN "No"
WHEN (ifc(t2.Account_Security_CD in ("1","2","5"),"Yes","No")) = "Yes" AND t2.Mortgage_Insurance = "No" AND (IFC (t2.MAX_of_ValuationDate > t7.Logical_Date_less_365,"Yes","No")) = "Yes" AND (ifc(t2.LVR>80,"Yes","No")) = "No" THEN "No"
WHEN (ifc(t2.Account_Security_CD in ("12"),"Yes","No")) = "Yes" AND t2.Mortgage_Insurance = "Yes" THEN "No"
WHEN (ifc(t2.Account_Security_CD in ("12"),"Yes","No")) = "Yes" AND (IFC (t2.MAX_of_ValuationDate > t7.Logical_Date_less_365,"Yes","No")) = "Yes" AND t2.Mortgage_Insurance = "No" AND (ifc(t2.LVR>80,"Yes","No")) = "No" THEN "No"
ELSE "Yes"
END
) = "Yes" AND
t2.DTR_Level5 IN ("Mortgage Loan" , "Security Only Loan" , "Line of Credit")
AND t2.AVG_of_Current_Balance > 0
THEN t2.AVG_of_Current_Balance
ELSE 0
END
END)
My concern is if I make a change to the Provisioning column then it will not automatically flow through to the other computed columns where I referred to the Provisioning computed column. I will need to change the code each time.
My question was whether it was possible for Enterprise Guide to accept a reference to the column rather than bring in the full code.
Thanks, Anna
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
Check out this tutorial series to learn how to build your own steps in SAS Studio.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.