Search code examples
sasreportsas-macro

Create a macro function by generate PROFILE report

enter image description here

enter image description here

data PROFILE;
input ID $ NAME $ Birthday $  Sex $  State $  Cust_Type $ Product $ Balance Last_tran_date $;
cards;
1001 John 1/1/1969 M CA Gold Checking 1000 9/1/2015
1002 Mary 2/1/2072 F CA Silver Saving 2000 10/1/2015
1003 Peter 3/1/1982 M NY Gold Loan 3000 10/3/2016
1004 Mary 4/1/1992 F NY Silver Checking 4000 9/17/2016
;
run;

data profile;
set profile;
today=DATE();
age = FLOOR((INTCK('month',birthday,today) - (day(today) < day(birthday)))/12);
year = year(last_tran_date);
drop today;
run;

******;
%let today=%sysfunc(today(),date9.);
%put &today;
%let report =Detail Listing Of Account;
%put &report;

%macro profile (title=, state=, age=, year=,);
proc report data=profile nowd colwidth=10 spacing=5 headline headskip;
column id name state age year balance;
compute before;
line @20 "title: &report" @68 "Run Date: &today ";
/* line @20 "state: &state " @45 "Age: <= &age " @68 "Last_Tran_Date: &year ";  */
line @20 "state: &state "  @68 "Last_Tran_Date: &year ";
endcomp;
compute after; 
line 60* "_";
line @40 'total balance ='
balance.sum dollar6.;
endcomp;
run;
%mend;


%profile(title=report,state="NY",age=39, year=2016);

I need the marco solution like:

 %profile(Title = Report, state = %str('NY', 'CA'), age = 18-40, year = 2016, Total_balance=on); 
%profile(age = 39, year = 2016, Total_balance=off);

Could anyone help me with the macro part with age range and total_balance on or off? I tried many times, still can not find the whole solution. Thank you so much!

Solution

  • The main thing you need to add is to use the parameter values to subset the data. That is easiest done by including a WHERE statement in your generated SAS code. It is even easier to include a series of WHERE ALSO statements.

    You also did not include two of the requested parameters.

    You probably should move the definition of TODAY into the macro also.

    So assuming that your existing PROC REPORT essentially works then your macro might be as simple as this:

    %macro profile 
(title=Detail Listing Of Account
,variables=id name state age year balance
,total=Y
,state=
,age=
,year=
);
%local today ;
%let today=%sysfunc(today(),date9.);

proc report data=profile nowd colwidth=10 spacing=5 headline headskip;
%if %length(&state) %then %do;
  where also state in (&state);
%end;
%if %length(&age) %then %do;
  where also age &age ;
%end;
%if %length(&year) %then %do;
  where also year(Last_tran_date) in (&year);
%end;
  column &variables;
  compute before;
    line @20 "title: &title" @68 "Run Date: &today ";
    line @20 "state: &state " @45 "Age: &age " @68 "Last_Tran_Date: &year ";  
  endcomp;
%if "&total"="Y" and %sysfunc(findw(&variables,BALANCE,,sit)) %then %do;
  compute after; 
    line 60* "_";
    line @40 'total balance ='
    balance.sum dollar6.;
  endcomp;
%end;
run;
%mend profile;

    Which if you call it with these settings:

    %profile(state='NY',age= >=40, year=2016);

    Will generate this program:

    MPRINT(PROFILE):   proc report data=profile nowd colwidth=10 spacing=5 headline headskip;
MPRINT(PROFILE):   where also state in ('NY');
MPRINT(PROFILE):   where also age >=40 ;
MPRINT(PROFILE):   where also year(Last_tran_date) in (2016);
MPRINT(PROFILE):   column id name state age year balance;
MPRINT(PROFILE):   compute before;
MPRINT(PROFILE):   line @20 "title: Detail Listing Of Account" @68 "Run Date: 22AUG2021 ";
MPRINT(PROFILE):   line @20 "state: 'NY' " @45 "Age: >=40 " @68 "Last_Tran_Date: 2016 ";
MPRINT(PROFILE):   endcomp;
MPRINT(PROFILE):   compute after;
MPRINT(PROFILE):   line 60* "_";
MPRINT(PROFILE):   line @40 'total balance =' balance.sum dollar6.;
MPRINT(PROFILE):   endcomp;
MPRINT(PROFILE):   run;

    Now you can try it with other combinations of values:

    state='CA'
state='CA' 'NY'
age= <18
age= in (18:40)
year=2017

    Notice that unlike the example given in the problem description there is no need to include the comma between the list of state codes. And including the comma will make it much harder for the users to call the macro since comma is already used by the macro processor to separate the list of parameters.

    If you want to support users typing

    age= 18-40

    then you will have to make the macro smarter at how it handles the AGE parameter.