I have a dataset of real estate data. I created a macro that provides the means for credit score, interest rates, and down payment percentage based on the property type. How do I take the means of the outputs and create a new dataset that merges the four macro calls? SAS code attached.
You could try just adding a PROC APPEND to your macro so that you can aggregate the individual output datasets into a single output dataset.
But you can probably just get PROC MEANS to generate everything in one call if you setup the data properly.
To remove the BRANCH variable from the grouping variables for some of the PROPTYPE values you can just set it to missing.
Looks like this is what you want to do;
data for_analysis ;
set loanapp;
if PropType in (1,2,3,4);
if PropType = 1 then cutoff = 800000;
if PropType = 2 then cutoff = 800000;
if PropType = 3 then cutoff = 1000000;
if PropType = 4 then cutoff = 1200000;
if price > cutoff ;
if not (PropType=1) then call missing(branch);
run;
proc means data=for_analysis mean nway missing;
class proptype cutoff branch ;
var CreditScore Interest PercentDown;
output out = loan_out;
Title1 "Mean of Credit Score, Interest Rate, and Down Payment";
Title2 "of Property with Sale Price Greater than CUTOFF";
run;
Include your code in the posts directly please.
Many people won't download attachments and they can't be opened automatically on many devices.
There's no need for macros here.
Take your data set, then create a new data set where the value of creditscore and interest and percentdown is set to missing if the price is <=800000 when proptype=1; when proptype is 2, do the same; when proptype is 3, set those variables to missing when price<=1000000, etc.
Then run PROC SUMMARY where you state the CLASS variable is Proptype, and the VAR variables are creditscore and interest and percentdown.
This is sooooo much easier than doing it by macros.
%Macro loan2(PropType=,Price=);
@RBRoma wrote:I have a dataset of real estate data. I created a macro that provides the means for credit score, interest rates, and down payment percentage based on the property type. How do I take the means of the outputs and create a new dataset that merges the four macro calls? SAS code attached.
%IF &PropType = 1 %THEN %DO;
PROC SORT data = loanapp;
BY Branch;
run;
PROC MEANS Data = loanapp mean;
VAR CreditScore Interest PercentDown;
Where PropType = &PropType AND Price > &Price;
By Branch;
Title "Mean of Credit Score, Interest Rate, and Down Payment
of Property Type &PropType greater than &Price Sale Price";
output out = loan_out;
run;
%end;
%else %if &PropType ^= 1 %THEN %DO;;
PROC MEANS Data = loanapp mean;
VAR CreditScore Interest PercentDown;
Where PropType = &PropType AND Price > &Price;
Title "Mean of Credit Score, Interest Rate, and Down Payment
of Property Type &PropType greater than &Price Sale Price";
output out=loan_out;
run;
%end;
%Mend loan2;
Data loanapp;
set '/folders/myshortcuts/SAS/SAS HW #4/Data_Assignment4/loanapp.sas7bdat';
run;
%loan2(PropType = 1, Price = 800000);
%loan2(PropType = 2, Price = 800000);
%loan2(PropType = 3, Price = 1000000);
%loan2(PropType = 4, Price = 1200000);
You could try just adding a PROC APPEND to your macro so that you can aggregate the individual output datasets into a single output dataset.
But you can probably just get PROC MEANS to generate everything in one call if you setup the data properly.
To remove the BRANCH variable from the grouping variables for some of the PROPTYPE values you can just set it to missing.
Looks like this is what you want to do;
data for_analysis ;
set loanapp;
if PropType in (1,2,3,4);
if PropType = 1 then cutoff = 800000;
if PropType = 2 then cutoff = 800000;
if PropType = 3 then cutoff = 1000000;
if PropType = 4 then cutoff = 1200000;
if price > cutoff ;
if not (PropType=1) then call missing(branch);
run;
proc means data=for_analysis mean nway missing;
class proptype cutoff branch ;
var CreditScore Interest PercentDown;
output out = loan_out;
Title1 "Mean of Credit Score, Interest Rate, and Down Payment";
Title2 "of Property with Sale Price Greater than CUTOFF";
run;
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
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.
Ready to level-up your skills? Choose your own adventure.