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;
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
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;
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
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;
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.