topic Re: Data Summary in SAS Programming

Data Summary

Eugenio211 — Fri, 13 Dec 2019 13:32:05 GMT

Hi,

Can someone help me out in summarizing data?

this is my proc step:

proc sql;

create table RevenueData as
select Loan_key,business_day
,revenue
,BadDebt
,BD_Date as COYearMonth format yymon.
,Rev_Date as RevYearMonth format yymon.
from TOtRevenueBD

;quit;

I want to summarize this so that the result can be:

LoanKey TotRevenue RevYearMonth TotBadDebt COYearMonth

I used proc summary however it's not giving me my desired result.

Thanks a lot.

Re: Data Summary

PaigeMiller — Fri, 13 Dec 2019 13:37:28 GMT

@Eugenio211 wrote:

Hi,

Can someone help me out in summarizing data?

this is my proc step:

proc sql;

create table RevenueData as
select Loan_key,business_day
,revenue
,BadDebt
,BD_Date as COYearMonth format yymon.
,Rev_Date as RevYearMonth format yymon.
from TOtRevenueBD

;quit;

I want to summarize this so that the result can be:

LoanKey TotRevenue RevYearMonth TotBadDebt COYearMonth

I used proc summary however it's not giving me my desired result.

Thanks a lot.

Show us the code you used for PROC SUMMARY. Show us the results, and explain what is wrong.

Also, "summarize" is not a specific term ... do you want sums, or means, or percents, or something else?

Re: Data Summary

Eugenio211 — Fri, 13 Dec 2019 13:50:25 GMT

here is my proc summary step:

proc summary data=RevenueData noprint nway missing;
class loan_key COYearMonth RevYearMonth;
var BadDebt revenue;
output out = RevData (drop = _type_ _freq_) sum=;
run;

It does not summarize the data, the result is in multiple rows. (see attached). you will notice the multiple loan_keys

Re: Data Summary

Eugenio211 — Fri, 13 Dec 2019 13:51:20 GMT

I want to sum the data.

Re: Data Summary

PaigeMiller — Fri, 13 Dec 2019 14:37:49 GMT

Sorry, I cannot download and open Microsoft Office documents. Please show a portion of the results in your reply, and explain what is wrong.

Re: Data Summary

Reeza — Fri, 13 Dec 2019 16:38:57 GMT

You get multiple rows because CLASS tells SAS to do summaries based on each level of the variable in the CLASS statement.

If all you want is a total sum, you can use the following. If you want something else, show us the input and expected output and we can help you fill in the middle logic.

proc summary data=RevenueData noprint nway missing;
var BadDebt revenue;
output out = RevData (drop = _type_ _freq_) sum=;
run;

Re: Data Summary

ballardw — Fri, 13 Dec 2019 19:37:41 GMT

You should provide some starting data and what you expect the result to be along with any rules.

Providing undesired output without a very explicit description as to why it is undesired (such as the actual desired output) is relatively useless.

CLASS variables in Proc Summary (and means and many other SAS procedures) create groups of output. If you don't want each level of a variable to create a row of output then it likely doesn't belong on a Class statement. If you want to create a group of values from DATES such as calendar month for each year then apply an appropriate format in Proc Summary. Then the Formatted value will be used for the group. I suspect that is what you missed in your code:

format COYearMonth RevYearMonth yymon. ;

or similar.