topic summary report in New SAS User

summary report

Cbob03 — Tue, 03 Mar 2020 03:02:33 GMT

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!

Re: summary report

andreas_lds — Tue, 03 Mar 2020 06:12:14 GMT

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.

Re: summary report

ed_sas_member — Tue, 03 Mar 2020 10:41:32 GMT

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,

Re: summary report

Cbob03 — Thu, 05 Mar 2020 02:41:10 GMT

Thank you very much!!