Desktop productivity for business analysts and programmers

Code in SAS EG - Add new row in the middle of a report with a total number

Reply
N/A
Posts: 0

Code in SAS EG - Add new row in the middle of a report with a total number

Hi All,

I am new to SAS EG and I am having trouble coding on one of the components as I have limited experience in programming.

One of the reports which is generated in SAS and is exported to Excel contains the following columns and rows:

Row Number Name Revenue Balance
1 Alex 50 30
.
.
5689 Julia 90 45

I need to add two new rows which would be after the 220th row and also the total of the revenue and balance in the SAS report before it is exported to excel.

Thanks,
PH
SAS Super FREQ
Posts: 8,721

Re: Code in SAS EG - Add new row in the middle of a report with a total number

Hi:
What is so special about the 220th row?? Also, I'm confused. after row 220, you would want to see one new row or two new rows???? At first I thought you wanted only one...but now, I'm not sure what would be on 2 new rows??? The sum of revenue and balance for rows 1-220 or the sum of revenue and balance for the WHOLE dataset???

Or do you want a subtotal after a group and then a grand total at the bottom of the report before it is exported to Excel??? Both PROC PRINT (LIST Data Task) and PROC REPORT (List Report Task) will generate grand totals and/or subtotals, but they generally work with "grouping" such as BY STATE or BY COUNTRY or some other logical category. Usually, SAS does not arbitrarily allow you to insert a summary line into the "middle" of a detail report, such as you describe.

Can you elaborate a bit more on what you need??? It looks like you could have almost 6000 observations displayed on your report and you want a summary line only after 220 observations????

cynthia
N/A
Posts: 0

Re: Code in SAS EG - Add new row in the middle of a report with a total number

Hi Cynthia,

I want to add the top 220 clients :sum of revenue and the balance for some specific purposes required by another team. All we want to do is insert one row or two (does'nt matter as we wanted to leave the 2nd row blank) which would give out the total of the revenue and balance for the top 1 to 220 clients.

We already have the report generating the grand total for 6000 rows and want to keep the grand total the way it is.

The format I am looking for is:

Row Number Name Revenue Balance

1 Alex 50 60
.
.
.
220 Cole 70 80
Total (1 to 220) (Sum of the revenue from 1 to 220) (Balance from 1 to 220)

222 Kazi 90 50
.
.
.
6000 Zach 20 30
(Grand totall from 1 to 6000)


I hope this make it a little clear.

Thanks a lot for the response and the help

PH
SAS Super FREQ
Posts: 8,721

Re: Code in SAS EG - Add new row in the middle of a report with a total number

Hi:
How do you know that there are only 220 "top" clients????

For example, in the sample data that you posted, Alex has a revenue of 50 and a balance of 60; while Kazi, who is row 222 has a revenue of 90 and a balance of 50; while Cole, row 220 has a revenue of 70 and a balance of 80...so why isn't poor Kazi in the top 220??? He's got more revenue than either Alex or Cole???

Generally, when you're ranking observations, it is on the basis of some numeric variable or calculated variable. Or you have some variable that is a category variable, like CUSTOMER GROUP where if the value is TOP or PLATINUM then they're in the TOP group and if the value for CUSTOMER_GROUP is STANDARD or GOLD, then they're not in the TOP Group, but in a lower group.

So is there a variable in your data that provides their category or is the category derived from some numeric variable?

cynthia
N/A
Posts: 0

Re: Code in SAS EG - Add new row in the middle of a report with a total number

Hi Cynthia,

Yes, thats right. We do rank the customers depending on the Customer_Group and they are classified according to platinum, gold and silver. Therefore the top 220 clients we are looking for is Platinum and the data provides this category.

Thanks,
PH
SAS Super FREQ
Posts: 8,721

Re: Code in SAS EG - Add new row in the middle of a report with a total number

Hi:
For a point and click method, using the List Data Task, you will want to use your Customer_Group variable as the "Group By" variable. So in the Variable Role pane, you assign all the other variables to the display role and the Customer_Group variable as the Group By variable for its role and next you can turn on the SUM for Revenue and Balance by specifying them as the variables to sum.

Then, the procedure that is "behind" the List Data Task (Proc PRINT) will automatically give you "subtotals" for each major Group ... Platinum, Gold and Silver -- with a Grand Total of all Groups at the bottom of the report. No counting required. If you have 220 Platinum in one month and 225 Platinum another time, using the Customer_Group variable to provide the "Group By" variable will allow the subtotal to happen at the right place.

For a bit more control over the report, such as being able to change the label on the summary line to the word Subtotal or Grand Total, or to add a blank line between major groups or to use the Customer_Group variable for producing the summary line ... but hiding the Customer_Group variable on the report itself, you would then need to use PROC REPORT.

If you want to lump everybody into 2 categories, instead of 3, you could then have a user-defined format applied to Customer_Group, so that you got the Platinum folks on the report by themselves and the Gold and Silver Folks lumped together. This would give you the Platiinum group and the rest of the folks on your report. The user-defined format approach is nice because it doesn't change the "internal" value of Customer_Group, but you allow you to lump Gold and Silver customers together for purposes of this one report.

cynthia
Ask a Question
Discussion stats
  • 5 replies
  • 195 views
  • 0 likes
  • 2 in conversation