BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
KateBA715
Fluorite | Level 6

I am (SUPER NEW) using SAS Studio ODA, 9.4, 

I have a data set that I have cleaned and reduced from 5000 obs to 445, and now have the following variables: Branch AVGLoanAmt AVGPrice MedCreditScore 

Within the Branch variable I have 5 values (1-5).(sorted by desc Branch)

I need to calc each Branch by the Count, AVGLoanAmt,AVGPrice, and MedCreditScore. PER Branch

**(Branch has Labels Branch(1=LIV924, 2=STV408, 3=SLO805, 4=GLN626, 5=COR760)

I have not used the Labels to create new variables, I have only tried PROC FREQ, PROC MEANS and PROC PRINT to break the data set up in order to do the various calcs for one table.)**

Here is the exact question asked and my last code:

Create a table that calculates the count, mean loan amount, mean home price, and median credit score, by branch

Everything I have tried is still giving me multiple rows of data with each Branch. And I have to do a PP Presentation for each step, as well, so I know there are possibly more steps than necessary.

DATA want;
SET have;
KEEP Branch LoanAmt Price CreditScore LoanApproved PercentDown;
WHERE PercentDown < 0.05;RUN;

DATA want;
SET have;
WHERE LoanApproved = 1; IF LoanApproved = 0 THEN DELETE;
RUN;

DATA want;
SET have;
AVGLoanAmt = Mean(LoanAmt);
AVGPrice = Mean(Price);
MedCreditScore = Median(CreditScore);
Format AVGLoanAmt AVGPrice Dollar16.2;
FORMAT MedCreditScore 3.;
KEEP Branch AVGLoanAmt AVGPrice MedCreditScore;
RUN;

PROC SORT data=have OUT= Want;
By DESCENDING Branch descending AVGLoanAmt descending AVGPrice descending MedCreditScore;
RUN;

Then I get stuck. 

I really just need to see how to separate and then calculate per branch (Branch1-Branch5) per argument.

ANY HELP WOULD BE SO VERY APPRECIATED! 

1 ACCEPTED SOLUTION
2 REPLIES 2
ballardw
Super User

Your requirement:

Create a table that calculates the count, mean loan amount, mean home price, and median credit score, by branch

does not have any requirement for LoanApproved. So why is it included in any of your code? Or PercentDown

You also are creating two different data "want" and the second one replaces the first.

This code:

DATA want;
SET have;
AVGLoanAmt = Mean(LoanAmt);
AVGPrice = Mean(Price);

Is calculating the "mean" of a single observation and one variable, i.e. the result is the same as LoanAmt.

 

It would help to provide actual example data.

 

Per Branch would typically mean use of a BY or Class variable, depending on the procedure chosen.

By "table" do you mean a data set for the result or a report that people read?

Proc means can likely do both with depending on options. This creates a data set.

Proc means data=have nway noprint;
   class branch;
   var loanamt creditscore price;
  output out=want mean(loanamt price)=AvgLoanamt AvgPrice
                            median(creditscore)=MedCreditScore
  ;
run;

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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