Count and Sum by if data exists in column then transpose.

Accepted Solution Solved
Reply
Contributor
Posts: 21
Accepted Solution

Count and Sum by if data exists in column then transpose.

Good Day All,

Currently I have a table of data where the columns are different types of credits.  Each row has a case number and an amount of credit for the type of credit issued for that case.  Not all columns have entries for each row.  I am trying to understand if there is a way to take the column names and make those rows, and then do a count for each time there is an entry under that header and a sum for the amounts that appear as well.  I have attached a spreadsheet with a few examples of what the data looks like and what I would like the output to look like.


Accepted Solutions
Solution
‎10-21-2014 05:07 PM
Super User
Posts: 18,997

Re: Count and Sum by if data exists in column then transpose.

Try Proc means, though it does become a two step process.

proc means data=have N SUM stackods;

var case_amt rebill_amt write_off mis_chrg_amt nlds_amt rev_cb trans_tgd_error cm_liabilty;

ods output summary=Want (Where=(N>0));

run;

Proc Print data=Want;

run;

View solution in original post


All Replies
Trusted Advisor
Posts: 1,228

Re: Count and Sum by if data exists in column then transpose.

Hi,

Not sure, why you want transpose. This can also be done as

proc tabulate data=have;

var case_amt rebill_amt write_off mis_chrg_amt nlds_amt rev_cb trans_tgd_error cm_liabilty;

table case_amt rebill_amt write_off mis_chrg_amt nlds_amt rev_cb trans_tgd_error cm_liabilty,n sum;

run;

Contributor
Posts: 21

Re: Count and Sum by if data exists in column then transpose.

Maybe transpose was the wrong choice of word but I wanted to see them in rows so there was only 3 columns as oposed to just doing a count and sum which would show 16 or so columns.

The proc tabulate worked great.  Only thing I have is for some reason in the data table, there a few instances of 0 instead of blanks.  The 0 is being counted in the count when it should be not.  Is there a way to do the tabulate where it only grabs it if its >0?

Solution
‎10-21-2014 05:07 PM
Super User
Posts: 18,997

Re: Count and Sum by if data exists in column then transpose.

Try Proc means, though it does become a two step process.

proc means data=have N SUM stackods;

var case_amt rebill_amt write_off mis_chrg_amt nlds_amt rev_cb trans_tgd_error cm_liabilty;

ods output summary=Want (Where=(N>0));

run;

Proc Print data=Want;

run;

Trusted Advisor
Posts: 1,228

Re: Count and Sum by if data exists in column then transpose.

As Reeza has suggested a good solution using stackods. If you still want to try proc tabulate then replace 0 values with missing. Not sure what may be the consequences for that but you can try something like this.

data have;
   set have;
   array list _numeric_;
            do over list;
            if list=0 then list=.;
end;
run;


proc tabulate data=have;
var case_amt rebill_amt write_off mis_chrg_amt nlds_amt rev_cb trans_tgd_error cm_liabilty;
table case_amt rebill_amt write_off mis_chrg_amt nlds_amt rev_cb trans_tgd_error cm_liabilty,n sum;
run;

Contributor
Posts: 21

Re: Count and Sum by if data exists in column then transpose.

This worked great I was able to get rid of all the 0's.  The last question I have is when I try to output the file to a dataset, as I need this to be exported to a CSV file.  the data set does not look like the table in the proc tabulate. It makes it one row with just all the data.  is there a way to put the actual table setup into a csv file or data set in that format?

When I do the proc tabulate I get

                              N          Sum

case_amt               20          5231

Rebill_amt              15          6532

write_off                  10          6532

however when I do the output to data set I get

_TYPE__PAGE__TABLE_case_amt_Ncase_amt_Sumrebill_amt_Nrebill_amt_Sumamex_write_off_Namex_write_off_Sum
011386921411424.2610234377592.22342516524052.86
Super User
Posts: 18,997

Re: Count and Sum by if data exists in column then transpose.

Use my proc means solution and export out the WANT data set generated instead.

Proc tabulate is great for displaying reports but not so much for generating data sets to export.

Contributor
Posts: 21

Re: Count and Sum by if data exists in column then transpose.

Thank you so much.  Once I replaced the proc tabulate and used the proc means.  I was able to output this perfectly how I wanted to a CSV file.

Trusted Advisor
Posts: 1,228

Re: Count and Sum by if data exists in column then transpose.

Proc means would be the right option. I usually use ods tagsets to get output generated by proc tabulate as an excel file.

ods tagsets.excelxp file='C:\want.xls';

proc tabulate data=have;

var case_amt rebill_amt write_off mis_chrg_amt nlds_amt rev_cb trans_tgd_error cm_liabilty;

table case_amt rebill_amt write_off mis_chrg_amt nlds_amt rev_cb trans_tgd_error cm_liabilty,n sum;

run;

ods tagsets.excelxp close;

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 391 views
  • 6 likes
  • 3 in conversation