BookmarkSubscribeRSS Feed
AnnaBetts
Calcite | Level 5

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

3 REPLIES 3
Reeza
Super User
I'm having a hard time following this, can you post a picture that illustrates your issue? You can manually change the names/labels of computed columns when you build them in the query builder. Unfortunately, changing labels/names won't propagate through the project.
AnnaBetts
Calcite | Level 5

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


Column1.JPG
Reeza
Super User
I don't know if it's possible to set EG to use the CALCULATED key word instead of bringing in the entire calculation, but in your computed column you can reference CALCULATED VARIABLE_NAME instead of dragging the computed column from the variable list. EG does seem to maintain the calculated keyword/reference in this case.

I agree not ideal though, hopefully someone else has a better answer!

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

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!

Register Now

Creating Custom Steps in SAS Studio

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 4376 views
  • 3 likes
  • 2 in conversation