BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jtevans1019
Calcite | Level 5

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?

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

5 REPLIES 5
PGStats
Opal | Level 21

Use the keyword calculated

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


PG

PG
jtevans1019
Calcite | Level 5

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
Opal | Level 21

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
jtevans1019
Calcite | Level 5

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) 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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