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?
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
Use the keyword calculated
when Calculated Shock = 'Non-Agency' ...
PG
Thanks!
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;
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
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)
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.