Solved
Contributor
Posts: 21

# 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: 23,724

## 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;

All Replies
Posts: 1,270

## 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: 23,724

## 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;

Posts: 1,270

## 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_N case_amt_Sum rebill_amt_N rebill_amt_Sum amex_write_off_N amex_write_off_Sum 0 1 1 3869 21411424.26 1023 4377592.22 3425 16524052.86
Super User
Posts: 23,724

## 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.

Posts: 1,270

## 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.