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:
Proc Sql;
create table example_table as select
status,
bucket,
sum(npv)/sum(balance) as per_npv
from loans_all (where=((flag=1)
and (eff_loan ne .)))
group by status,
bucket;
Quit;
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
I'm not sue how you wish to use the bucket value, especially when it's in that format.
Please provide sample input and desired output to clarify your requirement.
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
I feel a bit stupid, now, but still not getting your alternate calculation.
The link you provided points to this thead...?
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.