Solved: Re: Write a macro

dmayurp · Posted 03-23-2021 11:06 PM

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 · Posted 03-24-2021 10:15 PM

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

View solution in original post

japelin · Posted 03-24-2021 12:28 AM

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 · Posted 03-24-2021 04:17 AM

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.

Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
The macro for direct download as ZIP
How to post code
Please vote for Provide Sequential Search Capability for Hash Objects
How to deal with locked files on UNIX

dmayurp · Posted 03-24-2021 07:56 PM

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 · Posted 03-24-2021 07:58 PM

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;