Hi all, this question is in reference to another post of mine with detailed example and desired output (but since I haven't received many responses I thought I'd lay out a more generic conceptual question here.
I have a Proc Sql setup like this:
create table example_table as select
sum(npv)/sum(balance) as per_npv
from loans_all (where=((flag=1)
and (eff_loan ne .)))
group by status,
Everything works fine. Except, once in a while, the dataset loans_all contains observations that are missing "npv" and "balance" values. For those observations I want to tell SAS to look up that observation's "bucket" information and take the sum of npvs that with that bucket assignment/sum of all balances with that bucket assignment as the per_npv for that observation. For example:
Observation: Row 2
npv = .
balance = .
status = 5
bucket = 100-110%
The proc step I wrote above results in per_npv is missing. So I want SAS to move on, ignore the status and sum every npv in the data set associated with bucket=100-110% and divde that by every balance in the dataset associated with bucket=100-110% as the per_npv for this example row.
How would I do this? nested case statements? any examples or thoughts?
Thank you all for your time!
(link to original set up and example dataset: https://communities.sas.com/t5/Community-Matters/proc-sql-group-by-case-statement/m-p/272782#M1655
Hi LinusH thank you for your feedback. Bucket is a character so I just want to say take sumNPV/sumBalance for all character "bucket" (in this case for all bucket=100-110%.
All my sample input and desired output with pictures and data can be found in my original post on this matter: https://communities.sas.com/t5/Community-Matters/proc-sql-group-by-case-statement/m-p/272782#M1655
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.