Proc Report

[Eugenio211]

Hi - I have this code  below and what I want to do is to add another column by dividing 'converted' over 'customer count'.  is this possible? thanks.

proc report data=Test_List;
columns dm location campaign_month, (customer_count converted);
define dm/group;
define location/group;
define campaign_month/across 'LOC Preapprovals' order=data;
define customer_count/sum 'Preapprovals';
define converted/sum 'Conversion';
break after DM/Summarize;
rbreak after/Summarize;
compute after dm;
call define (_ROW_,"Style","Style=[fontweight=bold]");
DM = 'Subtotal';
endcomp;
compute after;
call define (_ROW_,"Style","Style=[fontsize=3 fontweight=bold]");
DM = 'Total';
endcomp;
run;

[Ksharp]

No. you can't .
You need calculated it by a data step within table 'Test_List' .

[Eugenio211]

Thank you.  I did that previously, however when it comes to the final output, the calculation of the 'Total' for 'Conversion Rate' is off, is there a work around for this one?

Jan 2021
Preapprovals	Conversion	Conversion Rate
47599	2467	757.16%

proc report data=LOCTest_List;
columns dm location campaign_month , (customer_count converted ConversionRate);
define dm/group;
define location/group;
define campaign_month/across 'LOC Preapprovals' order=data;
define customer_count/sum 'Preapprovals';
define converted/sum 'Conversion';
define ConversionRate/sum 'Conversion Rate';
break after DM/Summarize;
rbreak after/Summarize;
compute after dm;
call define (_ROW_,"Style","Style=[fontweight=bold]");
DM = 'Subtotal';
endcomp;
compute after;
call define (_ROW_,"Style","Style=[fontsize=3 fontweight=bold]");
DM = 'Total';
endcomp;
run;

[Cynthia_sas · Accepted solution]

Hi:

  I'm not sure exactly what you need/want, since you did not post any data to test your code with. You can create a new column under an ACROSS item using PROC REPORT. However, you would need to NOT pre-calculate the value and you would need to use the "absolute" column references for the variables under the ACROSS item, as shown below:

  I used SASHELP.PRDSALE and made some fake data. Luckily, SASHELP.PRDSALE has a MONTH variable that is a date value and can be formatted to MONYY5. to loosely correspond to what you show as your ACROSS item. Then, I only selected the first 3 months of each year (or Quarter=1) for my subset so that the ACROSS columns would fit in a screen shot. Then, I added a variable CALCRATE inside the parentheses and included a DEFINE statement for CALCRATE as a COMPUTED item.

  In the COMPUTE block for CALCRATE you can see how I calculate the percentage for each of the cells by dividing converted by customer_count for each ACROSS. You must use the absolute column numbers for this type of calculated column on the report. If this is what you need to do, then it is possible with PROC REPORT. Since you didn't post data, it's hard to tell what your code is doing.

  For more information about using ABSOLUTE column numbers with PROC REPORT, please refer to this paper: https://support.sas.com/resources/papers/proceedings14/SAS388-2014.pdf and to the PROC REPORT documentation.

Cynthia

[Eugenio211]

this works great, thank you so much!

[Ksharp]

Cynthia,
I would not like to use "absolute" column references .
Once the data structure changed , the code might be wrong .

[Cynthia_sas]

Hi:
You have to be sure of your data structure. And in my paper, I refer to Allison's paper that contains a macro program example to dynamically determine the number of across items and the absolute column numbers and then you just have to write the model code and the macro program generates the correct number of statements you need. It is a very clever example of macro programming with PROC REPORT. However, pre-summarizing is always an option. But in all my time using PROC REPORT with ACROSS variables, the times I've really needed to presummarize are few and the number of times that I've use absolute column numbers for production reporting on production files where the structure is fixed -- I've used absolute column number techniques many times on production reports.
Cynthia

[Ksharp]

Cynthia,

Why not let data step do these cumbersome stuff , as you said 'pre-summarizing' ?
"absolute" column references would cost more code than data step , and it might lead to wrong result if the number of month is changing for your example .

[Cynthia_sas]

Hi:
I've been using a version of the macro in Allison's paper to dynamically account for the column numbers and the program adjusts whether there are 3 values across or 30 values across. I've never gotten the wrong result after I've done my initial testing.

Although I do have to say that if there are 30 across and no calculations, my tendency is to use TABULATE.

Don't get me wrong, I love the DATA step, but I am also comfortable with how PROC REPORT summarizes and uses absolute column numbers, so although I understand your point of view, I'll probably continue using PROC REPORT the way I described in my paper. It's just too much fun to see what I can do with PROC REPORT to avoid presummarizing and making extra passes through the data.

Cynthia

[Eugenio211]

Hi Cynthia - I just want to say thank you for the code. It's working great for the report that I created.

[Eugenio211]

Hi Cynthia - I hope you don't mind asking your expertise again.  

below is the data that I have that I want to do another proc report.  I just needed to calculate multiple columns from the said data using proc report and do a summary at the bottom.  I tried to follow your previous code but it did not work for me.  Thanks a lot!

RD	DM	LOCATION	Market	Launch_Date_PDL_IL_Count	Current_PDL_IL_Count	Current_LOC_Count
RD1	DM1	1	British Columbia	766	395	612
RD1	DM1	2	British Columbia	374	365	254
RD1	DM1	3	British Columbia	745	354	439
RD1	DM1	4	British Columbia	631	306	536
RD1	DM1	5	British Columbia	931	442	535
RD1	DM1	6	British Columbia	908	469	664
RD1	DM1	7	British Columbia	418	203	465
RD1	DM1	8	British Columbia	664	302	461
RD1	DM1	9	British Columbia	510	265	305
RD1	DM2	10	British Columbia	440	212	321
RD1	DM2	11	British Columbia	692	253	470
RD1	DM2	12	British Columbia	1158	554	696
RD1	DM2	13	British Columbia	812	381	260
RD1	DM2	14	British Columbia	1021	413	690
RD1	DM2	15	British Columbia	559	273	375
RD1	DM2	16	British Columbia	827	361	491
RD1	DM3	17	British Columbia	781	336	841
RD1	DM3	18	British Columbia	645	294	341
RD1	DM3	19	British Columbia	837	391	460
RD1	DM3	20	British Columbia	596	283	412
RD1	DM3	21	British Columbia	727	368	477
RD1	DM3	22	British Columbia	817	448	362
RD1	DM3	23	British Columbia	551	248	428
RD1	DM3	24	British Columbia	882	514	524
RD2	DM4	25	Nova Scotia	409	331	426
RD2	DM4	26	Nova Scotia	356	332	337
RD2	DM4	27	New Bruinswick	497	211	246
RD2	DM4	28	Nova Scotia	297	230	315
RD2	DM4	29	Nova Scotia	234	213	246
RD2	DM4	30	Nova Scotia	312	261	448
RD2	DM5	31	Other Ontario	841	251	1334
RD2	DM5	32	Other Ontario	519	187	893
RD2	DM5	33	Other Ontario	841	211	1087
RD2	DM5	34	Other Ontario	265	83	403
RD2	DM5	35	Other Ontario	474	139	553
RD2	DM5	36	Other Ontario	473	125	758
RD2	DM5	37	Other Ontario	185	100	469
RD2	DM6	38	Other Ontario	739	208	638
RD2	DM6	39	Other Ontario	737	143	650
RD2	DM6	40	Other Ontario	458	92	457
RD2	DM6	41	Other Ontario	251	60	372
RD2	DM6	42	Other Ontario	374	103	573
RD2	DM6	43	Northern Ontario	1034	275	1001
RD2	DM6	44	Other Ontario	538	126	810
RD2	DM7	45	Other Ontario	622	167	901
RD2	DM7	46	Other Ontario	851	248	1139
RD2	DM7	47	Other Ontario	582	187	649
RD2	DM7	48	Other Ontario	308	105	462
RD2	DM7	49	Other Ontario	354	99	410
RD2	DM7	50	Other Ontario	427	128	764
RD2	DM7	51	Other Ontario	417	124	592
RD2	DM7	52	Other Ontario	320	126	557
RD2	DM8	53	Other Ontario	800	250	1005
RD2	DM8	54	Northern Ontario	759	179	1078
RD2	DM8	55	Other Ontario	369	131	465
RD2	DM8	56	Other Ontario	779	218	1124
RD2	DM8	57	Other Ontario	561	133	852
RD2	DM8	58	Other Ontario	437	122	483
RD2	DM8	59	Northern Ontario	792	222	836
RD2	DM8	60	Other Ontario	713	206	819
RD2	DM8	61	Other Ontario	249	75	340
RD2	DM9	62	Other Ontario	1100	262	1194
RD2	DM9	63	Other Ontario	420	132	735
RD2	DM9	64	Other Ontario	807	173	906
RD2	DM9	65	Other Ontario	388	134	504
RD2	DM9	66	Other Ontario	444	148	703
RD2	DM9	67	Other Ontario	480	135	770
RD2	DM9	68	Other Ontario	208	81	332
RD2	DM9	69	Other Ontario	378	101	638
RD2	DM10	70	Other Ontario	543	142	558
RD2	DM10	71	Other Ontario	549	118	593
RD2	DM10	72	Other Ontario	546	118	620
RD2	DM10	73	Other Ontario	461	169	495
RD2	DM10	74	Other Ontario	431	140	579
RD2	DM10	75	Northern Ontario	649	206	630
RD2	DM10	76	Other Ontario	322	134	423
RD2	DM10	77	Northern Ontario	295	96	412
RD2	DM10	78	British Columbia	187	0	79
RD2	DM10	79	British Columbia	462	0	215

and this is the proc report output that I want to generate.

RD	DM	Location	Launch Date PDL Count	Current PDL Count	Current LOC Count	Current Total	%of Book Converted	Drop in PDL	%Drop in PDL	Increase Overall	%Increase

Current_Total = Current_PDL_IL_Count + Current_LOC_Count
Perc_of_Book_Converted = Current_LOC_Count/Launch_Date_PDL_IL_Count
Drop_in_PDL_IL = Current_PDL_IL_Count - Launch_Date_PDL_IL_Count
Perc_Drop_in_PDL_IL = Drop_in_PDL_IL / Launch_Date_PDL_IL_Count
Increase_Overall = Current_Total - Launch_Date_PDL_IL_Count
Perc_Increase = Increase_Overall/Launch_Date_PDL_IL_Count

here is the code that I currently have.

proc report data=TEST;
columns rd dm location Launch_Date_PDL_IL_Count Current_PDL_IL_Count
Current_LOC_Count Current_Total Perc_of_Book_Converted
Drop_in_PDL_IL Perc_Drop_in_PDL_IL Increase_Overall Perc_Increase;
define rd/'RD' style(column)=[cellwidth=3cm];
define dm/'DM' style(column)=[cellwidth=3cm];
define Location/group 'Location';
define Launch_Date_PDL_IL_Count/'Launch Date PDL Count';
define Current_PDL_IL_Count/'Current PDL Count';
define Current_LOC_Count/'Current LOC Count';
run;

[Cynthia_sas]

Hi:

  This is not an ACROSS question. I don't see any ACROSS usage in your code. However, you need to read about PROC REPORT and how it works. I don't see any COMPUTE blocks in the code you've posted. My guess is that you tried this code maybe with one compute block or maybe with your formulas in open code. I would suspect that none of that worked. The 2 rules are these:

1) analysis variables need to use a compound name in a COMPUTE block

2) the left to right rule

  To illustrate  the first rule, look at the difference between #1 and #2 in trying to calculate the TOTAL column. Only #2 works when the correct compound name is used in the COMPUTE block.:

For the left to right rule, since you did not provide your data in a usable form and since I am teaching and can't make data, I'm again using SASHELP.SHOES to illustrate the example: Consider examples 3 and 4 which illustrate the left-to-right rule of PROC REPORT:

  Example #3 does not work because the COLUMN statement lists CALCPCT on the report row before listing RETURNS and SALES variables. If the COMPUTE block for CALCPCT needs the values for both RETURNS and SALES, then either CALCPCT needs to be on the COLUMN statement after RETURNS and SALES, which is easy to do. But what if you have a manager who insists that CALCPCT has to appear first? Then you need to use the left-to-right rule to your advantage. You can use a "helper" variable, any variable and place it at the end of the row. At that point in time, PROC REPORT has visibility of all the values on the report row and can go back and put a value into the CALCPCT cell as shown in Example 4.

  If your questions about about different code and represent a new example with different data, then it would be better to start a new posting. Always help people help you by posting ALL the code you've tried and the data in usable form, not just pasted in. Illustrating your point with a SASHELP dataset or writing a DATA step program that will create the data for your question is the fastest way to get help in the future.

Cynthia