topic Re: PROC SQL Nested CASE or DATA STEP DO LOOP in SAS Procedures

PROC SQL Nested CASE or DATA STEP DO LOOP

blakezen — Tue, 24 May 2016 19:00:56 GMT

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

Re: PROC SQL Nested CASE or DATA STEP DO LOOP

LinusH — Tue, 24 May 2016 18:53:11 GMT

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.

Re: PROC SQL Nested CASE or DATA STEP DO LOOP

blakezen — Tue, 24 May 2016 19:00:30 GMT

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

Re: PROC SQL Nested CASE or DATA STEP DO LOOP

LinusH — Tue, 24 May 2016 19:00:16 GMT

I feel a bit stupid, now, but still not getting your alternate calculation.

The link you provided points to this thead...?