Write a macro

[dmayurp]

Hello everyone,

I want to write a macro that allows to specify:

1) Title of report (Listing of Account)

2) Variable to report (Acct_ID Name Age Balance Last_Tran_Date)

3) Whether a “Total Balance” (sum of balance) row is displayed

4) Selection criteria by state: “CA”, “NY”

5) Selection criteria by age: Age <=40

6) Selection criteria by year of Last_Tran_Date: 2016

- Additionally: all macro parameters should be optional. An omitted parameter should be interpreted to mean “ALL”

- please include a few representative example of how macro in invoked

[japelin]

First of all, do you have a plain program before turning it into a macro?
If you can provide test data to go along with it, I think you will get a response.

[Kurt_Bremser]

Step 1: have a working code without macro coding.

Please post your report code, along with some example data (in a data step with datalines). Include any TITLE and FOOTNOTE statements.

[dmayurp]

HI,

Here is what I have tried so far. Thank you for your help.

data Profile;
input Acct_ID 1-4 Name $ 6-11 Birthday $ 13-21 Sex $ 23-24 State $ 25-26
Cust_Type $ 28-33 Product $ 35-42 Balance 44-47 Last_Tran_Date $ 49-57;
Cards;
1001 John 1/1/1962 M CA Gold Checking 1000 9/1/2015
1002 Mary 2/1/1972 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
1005 Linda 5/1/1994 F WA Gold Saving 5000
1006 Susan 6/1/1997 F WA Gold Loan 1000 9/15/2016
1007 Sam 7/1/1999 M CA Gold Checking 2000 2/1/2016
1008 Thomas 8/1/1962 M CA Silver Checking 3000 7/1/2016
1009 Linda 9/1/1982 F NY Silver Saving 4000
;
run;

data profile1;
set profile;
Birthday1 = input (Birthday, mmddyy10.);
Last_Tran_Date1 = input (Last_Tran_Date, mmddyy10.);
Tran_Year = year (Last_Tran_date1);
run;

data profile2;
set profile1;
today = date();
days = today -birthday1;
age = floor (days/365);
drop today days birthday birthday1;
run;

*PROC PRINT--------------------------------------------------------------------------------------------------------------------------;

%macro final (title, state1 =, state2 =, age =, tran_year =, var = );

data profile3;
retain &var;
set profile2;
if tran_year ne .;
where age &age and Tran_Year = &Tran_year and (State = "&state1" or state = "&state2");
keep acct_ID Name Age Balance Last_Tran_Date tran_year;
run;

ods rtf
file = "/folders/myfolders/macro.rtf";

proc sort data = profile3 out = profile4;
by tran_year;
run;

proc print data = profile4 noobs sumlabel= "Total Balance" grandtotal_label= "Grand Total" width=uniformby;
by tran_year;
sum balance; *need macro for total balance
var &var;
title2 j=l "Title: &title" j=r Run date: "%sysfunc(date(),worddate.)";
title3 j=l "State: &state1, &state2" j=c "Age &age" j=r "Tran Year: &tran_year";
run;

ods rtf close;

%mend final;

%final (Detail Listing of Account, state1 = CA, state2 = NY, age <=40, tran_tear = 2016, Acct_ID Name Age Balance Last_Tran_Date);

[dmayurp]

Here is the data that I am working on it.

data Profile;
input Acct_ID 1-4 Name $ 6-11 Birthday $ 13-21 Sex $ 23-24 State $ 25-26
Cust_Type $ 28-33 Product $ 35-42 Balance 44-47 Last_Tran_Date $ 49-57;
Cards;
1001 John 1/1/1962 M CA Gold Checking 1000 9/1/2015
1002 Mary 2/1/1972 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
1005 Linda 5/1/1994 F WA Gold Saving 5000
1006 Susan 6/1/1997 F WA Gold Loan 1000 9/15/2016
1007 Sam 7/1/1999 M CA Gold Checking 2000 2/1/2016
1008 Thomas 8/1/1962 M CA Silver Checking 3000 7/1/2016
1009 Linda 9/1/1982 F NY Silver Saving 4000
;
run;

data profile1;
set profile;
Birthday1 = input (Birthday, mmddyy10.);
Last_Tran_Date1 = input (Last_Tran_Date, mmddyy10.);
Tran_Year = year (Last_Tran_date1);
run;

data profile2;
set profile1;
today = date();
days = today -birthday1;
age = floor (days/365);
drop today days birthday birthday1;
run;

*PROC PRINT--------------------------------------------------------------------------------------------------------------------------;

%macro final (title, state1 =, state2 =, age =, tran_year =, var = );

data profile3;
retain &var;
set profile2;
if tran_year ne .;
where age &age and Tran_Year = &Tran_year and (State = "&state1" or state = "&state2");
keep acct_ID Name Age Balance Last_Tran_Date tran_year;
run;

ods rtf
file = "/folders/myfolders/macro.rtf";

proc sort data = profile3 out = profile4;
by tran_year;
run;

proc print data = profile4 noobs sumlabel= "Total Balance" grandtotal_label= "Grand Total" width=uniformby;
by tran_year;
sum balance; *need macro for total balance
var &var;
title2 j=l "Title: &title" j=r Run date: "%sysfunc(date(),worddate.)";
title3 j=l "State: &state1, &state2" j=c "Age &age" j=r "Tran Year: &tran_year";
run;

ods rtf close;

%mend final;

%final (Detail Listing of Account, state1 = CA, state2 = NY, age <=40, tran_tear = 2016, Acct_ID Name Age Balance Last_Tran_Date);

[japelin · Accepted solution]

How about this code?

%macro final (title=
             , state =
             , age =
             , tran_year =
             , var = 
             , sumvar = 
             );

data profile3;
  %if &var ne %then %do; /* I don't know if you need retain, but you can use empty parameters to make it work. */
  retain &var;
  %end;
  set profile2;
  if tran_year ne .;
  where %if &age ne  %then %do;
          age &age 
        %end;%else
        %do;
          age > 0
        %end;
        %if &Tran_Year ne  %then %do;
          and Tran_Year = &tran_year 
        %end;
        %if &State ne  %then %do;
          and (State in ("%sysfunc(TRANWRD(&state,%str(,)," "))"))
        %end;
        ;
  keep acct_ID Name Age Balance Last_Tran_Date tran_year;
run;

%if &title = %then %do;
  %let title=Detail Listing of Account;
%end;
%if &State =  %then %do;
  %let State=All;
%end;
%if &age =  %then %do;
  %let age=All;
%end;
%if &Tran_Year =  %then %do;
  %let tran_year=All;
%end;

ods rtf file = "/folders/myfolders/macro.rtf";

proc sort data = profile3 out = profile4;
  by tran_year;
run;

proc print data = profile4 noobs sumlabel= "Total Balance" grandtotal_label= "Grand Total" width=uniformby;
  by tran_year;
  %if %upcase(&sumvar)^=NO %then %do;
  sum balance; *need macro for total balance var &var;
  %end;
  title2 j=l "Title: &title" j=r "Run date: %sysfunc(date(),worddate.)";
  title3 j=l "State: &state" j=c "Age: &age" j=r "Tran Year: &tran_year";
run;

ods rtf close;

%mend final;

/* 2 sample patterns */
%final (title=Detail Listing of Account
       ,state =CA%str(,)NY
       ,age=%str(<=)40
       ,tran_year = 2016
       ,var=Acct_ID Name Age Balance Last_Tran_Date
       ,sumvar=yes
       );
%final (title=Detail Listing of Account
       ,state =
       ,age=
       ,tran_year = 
       ,var=
       ,sumvar=
       );

[dmayurp]

It worked. Thank you for your help.

[Aryyyan]

Thank you for your help