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
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=
);
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.
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.
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);
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);
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=
);
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.