topic How can I separate values from one variable in order to use the new vars for calculation? in SAS Software for Learning Community

How can I separate values from one variable in order to use the new vars for calculation?

KateBA715 — Tue, 15 Nov 2022 22:18:36 GMT

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!

Re: How can I separate values from one variable in order to use the new vars for calculation?

ballardw — Tue, 15 Nov 2022 22:50:41 GMT

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;

Re: How can I separate values from one variable in order to use the new vars for calculation?

Reeza — Tue, 15 Nov 2022 23:09:17 GMT

What tools have you learned to create summaries? Proc means/summary/univariate? A data step (I suspect not?) Summary Task?
If programming:
https://www.listendata.com/2015/01/sas-detailed-explanation-of-proc-means.html

If tasks:
https://video.sas.com/detail/video/5537540644001/the-summary-statistics-task-in-sas%C2%AE-studio?autoStart=true&q=summary%20task