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

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

jtevans1019 — Tue, 26 May 2015 15:43:24 GMT

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?

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

PGStats — Tue, 26 May 2015 15:50:31 GMT

Use the keyword calculated

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

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

jtevans1019 — Tue, 26 May 2015 16:40:00 GMT

Thanks!

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

jtevans1019 — Tue, 26 May 2015 17:01:40 GMT

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;

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

PGStats — Tue, 26 May 2015 17:09:51 GMT

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

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

jtevans1019 — Tue, 26 May 2015 17:32:02 GMT

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)