topic How to create a new dataset from macro output? in SAS Procedures

How to create a new dataset from macro output?

RBRoma — Tue, 21 Nov 2017 18:20:12 GMT

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.

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

Reeza — Tue, 21 Nov 2017 18:27:53 GMT

Include your code in the posts directly please.

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

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

PaigeMiller — Tue, 21 Nov 2017 18:38:24 GMT

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.

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

RBRoma — Tue, 21 Nov 2017 18:30:53 GMT

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

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

Tom — Tue, 21 Nov 2017 19:21:23 GMT

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;