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.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

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

View all other training opportunities.

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