topic Re: Will this code give me an expected result? in New SAS User

Will this code give me an expected result?

Sathiskumar_D — Thu, 25 Apr 2019 02:41:16 GMT

options symbolgen mlogic;

%macro accountreport (State=,Age=,Last_Tran_Date=);
%let title=Detail Listing of Account;
data profile3;
set myprj2.profile2 (keep=Acct_ID Name Birthday State Balance Last_Tran_Date);
/*Age=((int(today()-birthday)/365.25));*/
Age=intck('Year',birthday,today());
/*birthdate=input(birthday,mmddyy10.);*/

format Last_Tran_Date year4.
		Age 3.0
		Birthday best.;/*stored in numeric format*/
run;
options symbolgen mprint;
%macro dsn(title=, vars= );

proc report data=profile3;
	title "&title";
	column Acct_ID Name Balance Last_Tran_Date;
	define Acct_ID / display;
	define Name / display;
	define Balance / analysis sum;
	define Last_Tran_Date / display;
	rbreak after / summarize;
run;
%mend sat;

options mprint;
%dsn (vars=Acct_ID Name Balance Last_Tran_Date);

I am trying to create a report based on this format.Will this code work ?I am not sure how the title and the row followed by title will be achieved

Re: Will this code give me an expected result?

Kurt_Bremser — Thu, 25 Apr 2019 05:39:44 GMT

Please post example data in a usable form (data step with datalines, see my footnotes). Excel files cannot give accurate information about SAS datasets (contents, type, lengths, formats), and many corporate sites block their download for security reasons.

If you want to know if code works, follow Maxim 4. Try it.

Re: Will this code give me an expected result?

Astounding — Thu, 25 Apr 2019 05:54:06 GMT

There are basic features missing that prevent you from even testing the code.

Where is the %mend statement to finish the definition of %accountreport?

Where is %accountreport being executed?

How can this statement possibly finish the definition of %dsn:

%mend sat;

Re: Will this code give me an expected result?

Sathiskumar_D — Thu, 25 Apr 2019 14:16:43 GMT

data dsn;
infile datalines missover dsd;
length Acct_ID 4 Name$ 7 Birthday 8 Sex$ 3 State$ 3 Cust_Type$ 7 Product$ 9 Balance 4 Last_Tran_Date 8; 
input Acct_ID 1-4 Name $ 6-11 Birthday mmddyy10. Sex $ 21-23  State $ 24-26 Cust_Type $ 28-33 Product $ 35-43 Balance 4. Last_Tran_Date mmddyy12.;
datalines;
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;
proc print data=dsn;
format last_Tran_Date year4.;
run;

This is my data in datalinesMy expected result in the format is attached above.my code which is working but expecting in the format

options symbolgen mprint;
%macro dsn(title=, vars= );

proc report data=profile3;
	title "&title";
	column Acct_ID Name Balance Last_Tran_Date;
	define Acct_ID / display;
	define Name / display;
	define Balance / analysis sum;
	define Last_Tran_Date / display;
	rbreak after / summarize;
run;
%mend dsn;

Re: Will this code give me an expected result?

ballardw — Thu, 25 Apr 2019 14:44:43 GMT

No.

Re: Will this code give me an expected result?

ChrisHemedinger — Thu, 25 Apr 2019 15:06:53 GMT

It looks like you've left out a number of requirements/aspects of the data and your objective, but let me see if I can infer some.

You have this transaction data. You want to create multiple tables of output, separated by age range and by transaction year and maybe by US state.

Your approach of using SAS macro language may be complicating things. I'd first try to achieve this by using BY groups -- built into most SAS procedures.

You didn't include US state fields in your data sample, so leaving that off for now. To implement groups segmented by Age range (instead of discrete ages), you need to define another variable or use a SAS format. Here's an example with your data that defines an "age level" format -- two levels, one for under 40 and one for over 40.

data dsn;
infile datalines missover dsd;
length Acct_ID 4 Name$ 7 Birthday 8 Sex$ 3 State$ 3 Cust_Type$ 7 Product$ 9 Balance 4 Last_Tran_Date 8; 
input Acct_ID 1-4 Name $ 6-11 Birthday mmddyy10. Sex $ 21-23  State $ 24-26 Cust_Type $ 28-33 Product $ 35-43 Balance 4. Last_Tran_Date mmddyy12.;
Age=intck('Year',birthday,today());
format Last_Tran_Date year4.
		Age 3.0
		Birthday best.;/*stored in numeric format*/
datalines;
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;

proc format lib=work;
 value agelvl 0-39="< 40"
              40-high = ">= 40";
run;

proc sort data=dsn
 out=sorted;
 by age;
run;

proc report data=sorted;
	title "Detail Listing of Account";
  by age;
  format age agelvl.;
  label age="Age range";
	column Acct_ID Name Balance Last_Tran_Date;
	define Acct_ID / display;
	define Name / display;
	define Balance / analysis sum;
	define Last_Tran_Date / display;
	rbreak after / summarize;
run;

This needs more work, obviously. But my advice (consistent with what others have said) -- get things working without macro first. Maybe avoid macro language altogether and leverage the BY group capabilities in SAS procedures. SAS macro is powerful, but once you introduce it to your program, your program becomes that much more difficult to maintain and debug.