How to use previously created case variable (end as x) in new case statement (case when x) in same proc sql

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

How to use previously created case variable (end as x) in new case statement (case when x) in same proc sql

I have a sql procedure with about 5 "case whens" (case when _ then _ else _ end as _ ). I'm trying to figure out how I can take a variable created in the "end as _ " portion of the previous case statement and use it in the "case when _ " portion of the next case statement within the same proc sql.

Here's my code:

case

when a.type = 'Non-Agency' then 'Non-Agency'

when a.Cat = 'Municipal' then 'Municipal'

else 'Agency'

end as Shock,

case
when Shock = 'Non-Agency' and a.Cat = l.breakdown then l.Cat

else 'None'

end as Lookup,

So above, the first case statement creates the variable Shock. I want to reference Shock in the next case statement.

But the log says "ERROR: The following columns were not found in the contributing tables: Shock."

Besides starting a 2nd proc sql, how can I get around this?


Accepted Solutions
Solution
‎05-26-2015 01:09 PM
Respected Advisor
Posts: 4,820

Re: How to use previously created case variable (end as x) in new case statement (case when x) in same proc sql

Difficult to diagnose on an excerpt. Post the entire query.

Note that you can always (not sure) replace a calculated variable with the original expression.

- PG

PG

View solution in original post


All Replies
Respected Advisor
Posts: 4,820

Re: How to use previously created case variable (end as x) in new case statement (case when x) in same proc sql

Use the keyword calculated

when Calculated Shock = 'Non-Agency' ...


PG

PG
New Contributor
Posts: 4

Re: How to use previously created case variable (end as x) in new case statement (case when x) in same proc sql

Thanks!

New Contributor
Posts: 4

Re: How to use previously created case variable (end as x) in new case statement (case when x) in same proc sql

Follow-up question. I'm now getting an error saying: ERROR: The following columns were not found as CALCULATED references in the immediate query:  Lookup1.

Could this be because I'm using "Calculated Lookup1" in the "from __ " section at the bottom of the code? How do I fix this?

case
when a.Cat = 'Municipal' then a.credit_rtng

when a.Cat is null and (a.FAS115 || a.type || a.breakdown) = (l.FAS115 || l.type || l.breakdown) then l.FHFA_Template else a.Cat

end as Lookup1,

case when Calculated Lookup1 = m.Rating then m.Sev_Spread_bps else .

end as Muni_Shock,


case

when n.breakdown1 = Calculated Lookup2 and n.breakdown3 = Calculated Lookup1 and n.rating = a.credit_rtng

and n.vintage = a.vintage_yr then n.Sev_MV_Shock else .

end as NonAgency_Shock,


from GMSCusip.afs_group_master_bd a

left join Lookup l on a.breakdown = l.breakdown and a.FAS115 = l.FAS115 and a.type = l.type

left join GMS gm on a.breakdown = gm.breakdown

left join AgencyShock ag on Calculated Lookup1 = ag.Product

left join MuniShock m on Calculated Lookup1 = m.Rating

left join NonAgencyShock n on Calculated Lookup1 = n.breakdown3 and a.vintage_yr = n.vintage

;

quit;

Solution
‎05-26-2015 01:09 PM
Respected Advisor
Posts: 4,820

Re: How to use previously created case variable (end as x) in new case statement (case when x) in same proc sql

Difficult to diagnose on an excerpt. Post the entire query.

Note that you can always (not sure) replace a calculated variable with the original expression.

- PG

PG
New Contributor
Posts: 4

Re: How to use previously created case variable (end as x) in new case statement (case when x) in same proc sql

Thanks PGStats. I put the original expression in the from section in place of "Calculated Lookup1". Now it works!

from GMSCusip.afs_group_master_bd a

left join Lookup l on a.breakdown = l.breakdown and a.FAS115 = l.FAS115 and a.type = l.type

left join GMS gm on a.breakdown = gm.breakdown

left join AgencyShock ag on ag.Product =

     (case when a.Cat = 'Municipal' then a.credit_rtng

     when a.Cat is null and (a.FAS115 || a.type || a.breakdown) = (l.FAS115 || l.type || l.breakdown)

     then l.FHFA_Template else a.Cat

     end) 

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 438 views
  • 0 likes
  • 2 in conversation