summary report

[Cbob03]

Hello,

I have a input file(have.xlsx) that has ID by date. I am looking to produce summary report by date that has count of ID by different market groups(PHL,NUC,MKT,MRC,DRA). I need help with generating the desired output(results.xlsx) with totals at the end. I also want to output the data by each market group in the same file if the total of any market group is greater than zero. Basically print the rows with count greater than zero.

I am new to summary reporting so any help is greatly appreciated.

Thanks for your time!

[andreas_lds]

Please post the data you have as dataset using datalines, also post the expected result in the same form or as screenshot. I won't open any office-documents.

[ed_sas_member · Accepted solution]

Hi @Cbob03 

Here is an approach to achieve this form the input dataset HAVE:

proc means data=have sum noprint;
	var MKT PHL MRC DRA NUC;
	class dt;
	ways 1;
	output out=want (drop=_:) sum=MKT PHL MRC DRA NUC;
quit;

%macro print_market (market);
	ods excel options(sheet_name="&market");
	proc print data=have;
		title "&market";
		var dt;
		ID ID;
		where &market > 0;
	run;
%mend;

/* Report */

ods excel file='/PATH/results.xlsx' options (sheet_name="Summary") /* <--- update PATH */ ;

	proc print data=want;
		title "Summary";
		id dt;
		sum MKT PHL MRC DRA NUC;
	run;

	%print_market(MKT)
	%print_market(PHL)
	%print_market(MRC)
	%print_market(DRA)
	%print_market(NUC)
;

ods excel close;

Best,

[Cbob03]

Thank you very much!!