Proc SQL Summary Help

[megganwalker]

I am trying to prevent my summary totals and count totals from repeating in each row in an overall column.  The attached export shows what I am getting.  Below is the code I used.  I want to be able to have subtotals at the bottom of my details in one cell. 

PROC SQL;

CREATE TABLE WORK.'LOCAL_ADJ_TRACK'n AS

SELECT t1.IDNumber,

t1.Description,

t1.Status,

t1.ProblemIdDate,

t1.ClmsRecDate,

t1.TotalClaims,

t1.LocalPreEntry,

t1.LocalBDInitiated,

t1.LocalRCPInitiated,

t1.LocalSAInitiated,

t1.LocalClosed,

count (t1.IDNumber) as RespByDivision,

sum (t1.LocalPreEntry) as PreEntryClaims,

sum (t1.LocalPreEntry,t1.LocalBDInitiated,t1.LocalRCPInitiated,t1.LocalSAInitiated) as TotalClaims_Pre_Ini

FROM WORK.'ADJ_TRACK_IMPORT'n t1

WHERE t1.Status = 'Open' AND t1.LocalClosed = 'N'

order by t1.ProblemIdDate;

QUIT;

[Reeza]

Sorry, it's really unclear what you're asking here. 

Can you clarify in detail? 

Is your code not working? 

Or just not giving the desired results?
What does your input data look like? What do you want as output?

---

@megganwalker wrote:

I am trying to prevent my summary totals and count totals from repeating in each row in an overall column.  The attached export shows what I am getting.  Below is the code I used.  I want to be able to have subtotals at the bottom of my details in one cell. 

PROC SQL;

CREATE TABLE WORK.'LOCAL_ADJ_TRACK'n AS

SELECT t1.IDNumber,

t1.Description,

t1.Status,

t1.ProblemIdDate,

t1.ClmsRecDate,

t1.TotalClaims,

t1.LocalPreEntry,

t1.LocalBDInitiated,

t1.LocalRCPInitiated,

t1.LocalSAInitiated,

t1.LocalClosed,

count (t1.IDNumber) as RespByDivision,

sum (t1.LocalPreEntry) as PreEntryClaims,

sum (t1.LocalPreEntry,t1.LocalBDInitiated,t1.LocalRCPInitiated,t1.LocalSAInitiated) as TotalClaims_Pre_Ini

FROM WORK.'ADJ_TRACK_IMPORT'n t1

WHERE t1.Status = 'Open' AND t1.LocalClosed = 'N'

order by t1.ProblemIdDate;

QUIT;

---

[megganwalker]

I am sorry.  In my attachment it shows my summary with all 9's down the column in every cell.  I just want a summary at the bottom for any sum's or count's so that it has a subtotal at the bottom of my output not a full column with repeated values of the one number.

[tomrvincent]

you need a group clause. Also, remove the fields you are aggregating from the select statement (like localpreentry)

[megganwalker]

I understand the group clause but why would I remove the items I need to still have in my output.  I need to have all of those column show up as well with a subtotal.

[Reeza]

PROC SQL is not a reporting procedure. Use PROC PRINT or REPORT to generate a report with summarized data.

[megganwalker]

If it isn’t a reporting procedure then why do we use count and sum functions to summarize?

[Reeza]

You can calculate summary statistics but it doesn't automatically generate any subtotals. You'll have to do those manually then, and append them in using a UNION and then display as needed.

A 'reporting procedure' shows data in a more structured report type, ie what you'd show your boss.

SQL is a more generic proc that can do merges, unions and other data manipulation but it's main function isn't presenting data to an end user in a report format.

So if you want to continue to use SQL, you'll need to write another query to calculate the subtotals and then merge them in at the correct points and display them. Or PROC PRINT or REPORT or TABULATE can do that automatically. Your choice.

[ballardw]

---

@megganwalker wrote:
If it isn’t a reporting procedure then why do we use count and sum functions to summarize?

---

Proc SQL is more intended for data manipulation, especially involving combining records on complex matching requirements.

It isn't a "reporting procedure" because the output is not intended to make "nice" looking presentations for people to read.

Also suppose I want to create output with multiple statistics for a single variable. In Proc SQL you have to explicitly name each output variable in a select statement.

Proc tabulate you just put the requested statistic(s) in conjunction with the variable:

tables var1 var2 var3,

          n mean std range min max

;

makes a nice-ish table with 3 rows, one for each variable, and 6 columns, each one with a different statistic for each variable(row). The output includes such things as the name or label of variable on the left row heading. (This of course assumes that the proc statement points to a data set and appropriate VAR statement.

Proc SQL won't do the row / column behavior. You could create 18 additional variables with the statistics but the only display it will allow directly  is the 18 columns in some order. Which is not conducive to easy comparison of the statistics across the different variables for us humans.

[megganwalker]

So i could do a proc print and remove my count and sum commands all together ? Or keep them there and then proc print ? I will try two separate proc sql steps and merge but if proc print is easier then I need to look at how to do a proc print .

[ballardw]

---

@megganwalker wrote:
So i could do a proc print and remove my count and sum commands all together ? Or keep them there and then proc print ? I will try two separate proc sql steps and merge but if proc print is easier then I need to look at how to do a proc print .

---

It really depends on what you want.

Without input example data and what the actual final output should look like it is hard to make specific recommendations.

And you don't show how you create the spreadsheet. That was not done with Proc SQL.

If you can provide a small example data set, it does not need to have all of the variables, but enough to give an idea, maybe 5 or 6, indicating which variables might be considered grouping variables and which need to have values counted or summed, and a small number of rows so that you can manually show what the result for the example result might look like we might have better suggestions.

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

[Reeza]

I would also double check that this gives you what you expect, it would be the total per line, not across rows:
sum (t1.LocalPreEntry,t1.LocalBDInitiated,t1.LocalRCPInitiated,t1.LocalSAInitiated) as TotalClaims_Pre_Ini

PROC PRINT documentation has some examples of sum/counts.
http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/viewer.htm#a002262174.htm