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

[jtevans1019]

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?

[PGStats]

Use the keyword calculated

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

PG

[jtevans1019]

Thanks!

[jtevans1019]

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;

[PGStats · Accepted solution]

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

[jtevans1019]

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)