topic Re: Using Proc Report on Summarized Datasets in SAS Enterprise Guide

Using Proc Report on Summarized Datasets

Nero_IV — Mon, 24 Sep 2018 16:59:59 GMT

I have a summarized dataset in the below format to track the performance of an email campaign. Each of the flag columns have a binary value , and the total count of customers corresponding to that row is given in Count column.

EDIT : So, the first row signifies that there are 10 customers which were in Control set, who didn't open the email , didn't respond and were not funded.

The second row signifies 20 customers in Test set who didn't open email, didn't respond and were not funded.

End of EDIT

Campaign	Control_Flag	Email_Open	Responder	Funded	Count	$Revenue
1	Y	0	0	0	10
1	N	0	0	0	20
1	N	1	0	0	30
1	Y	0	1	0	40
1	N	0	1	0	50
1	N	1	1	0	60
1	Y	0	1	1	70	$ 700,000
1	N	0	1	1	80	$ 800,000
1	N	1	1	1	90	$ 900,000

I need to prepare the report in the below format from it. For Responders/Funded/Revenue rows, I just need the sum of counts where these flag values are 1. Total Test is the sum of Email Open and Email Not Open columns.

	Campaign 1
	Test
	Email Not Open	Email Open	Total Test	Control
Target	150	180	330	120
Responders	130	150	280	110
Funded	80	90	170	70
Revenue	$ 800,000	$ 900,000	$ 1,700,000	$ 700,000

Running the below code show the counts of both responders and non responders. How do I show counts of only those cases where the flag variable is 1?

proc report data=TARGETS2_G;

column resp control_flag,cust;

define resp / group;

define control_flag / across;

define cust /analysis sum;

run;

EDIT 2 : Uploaded the input file.

Re: Using Proc Report on Summarized Datasets

Reeza — Mon, 24 Sep 2018 15:25:32 GMT

It depends on what you mean by that, and it's hard to see from your example data. If you want to limit a field (revenue by flags) its a different story than if you just want the count of 1's. If you do, SUM() is the correct statistic, since SUM() with 0/1 is the same as Count(X=1).

Re: Using Proc Report on Summarized Datasets

Nero_IV — Mon, 24 Sep 2018 15:36:27 GMT

To fill in the cells, I need the sum of count column given that the Responder = 1 for one row, Funded = 1 for other row etc.
Sum of flag variable will only give me the count of rows, which is incorrect.

Re: Using Proc Report on Summarized Datasets

Reeza — Mon, 24 Sep 2018 15:44:51 GMT

Sounds like you may need to presummarize then or use a lot of COMPUTE statements within PROC REPORT.

For the summaries you want, you'll need to control the WAYS/TYPES statements on PROC MEANS/SUMMARY or customize it in PROC FREQ. This is assuming I actually understand what you want, which I'm not really sure about.

@Nero_IV wrote:
To fill in the cells, I need the sum of count column given that the Responder = 1 for one row, Funded = 1 for other row etc.
Sum of flag variable will only give me the count of rows, which is incorrect.

Re: Using Proc Report on Summarized Datasets

ballardw — Mon, 24 Sep 2018 15:57:29 GMT

Post your example data as a data step.

I think I have an idea what you might be wanting but I am not going to spend the time with the format the "data" was pasted to attempt to create a data set as it is very unfriendly.

Re: Using Proc Report on Summarized Datasets

Nero_IV — Mon, 24 Sep 2018 17:00:38 GMT

Uploaded the CSV file for input data.