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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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;

View solution in original post

4 REPLIES 4
Reeza
Super User

Include your code in the posts directly please. 

Many people won't download attachments and they can't be opened automatically on many devices. 

PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
RBRoma
Fluorite | Level 6

%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);

Tom
Super User Tom
Super User

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;

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
  • 4 replies
  • 2993 views
  • 4 likes
  • 4 in conversation