Help using Base SAS procedures

PROC SQL Nested CASE or DATA STEP DO LOOP

Reply
Contributor
Posts: 38

PROC SQL Nested CASE or DATA STEP DO LOOP

[ Edited ]

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 

Super User
Posts: 5,259

Re: PROC SQL Nested CASE or DATA STEP DO LOOP

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.

Data never sleeps
Contributor
Posts: 38

Re: PROC SQL Nested CASE or DATA STEP DO LOOP

[ Edited ]

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

Super User
Posts: 5,259

Re: PROC SQL Nested CASE or DATA STEP DO LOOP

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

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

Data never sleeps
Ask a Question
Discussion stats
  • 3 replies
  • 242 views
  • 0 likes
  • 2 in conversation