Help using Base SAS procedures

How to create a new dataset from macro output?

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

How to create a new dataset from macro output?

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.

Attachment

Accepted Solutions
Solution
‎11-21-2017 03:41 PM
Super User
Super User
Posts: 8,069

Re: How to create a new dataset from macro output?

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


All Replies
Super User
Posts: 23,663

Re: How to create a new dataset from macro output?

Include your code in the posts directly please. 

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

Respected Advisor
Posts: 2,981

Re: How to create a new dataset from macro output?

[ Edited ]

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
Occasional Contributor
Posts: 5

Re: How to create a new dataset from macro output?

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

Solution
‎11-21-2017 03:41 PM
Super User
Super User
Posts: 8,069

Re: How to create a new dataset from macro output?

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;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 509 views
  • 3 likes
  • 4 in conversation