05-24-2016 02:42 PM - edited 05-24-2016 03:00 PM
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
05-24-2016 02:53 PM
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.
05-24-2016 02:56 PM - edited 05-24-2016 03:00 PM
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
05-24-2016 03:00 PM
I feel a bit stupid, now, but still not getting your alternate calculation.
The link you provided points to this thead...?