BookmarkSubscribeRSS Feed
blakezen
Obsidian | Level 7

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 

3 REPLIES 3
LinusH
Tourmaline | Level 20

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
blakezen
Obsidian | Level 7

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

LinusH
Tourmaline | Level 20

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

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

Data never sleeps

SAS INNOVATE 2024

Innovate_SAS_Blue.png

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. 

Register now!

What is Bayesian Analysis?

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.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 1064 views
  • 0 likes
  • 2 in conversation