Transpose and sum

[pensarchem]

Hi All,  the below is the data set, how can I transpose and add the total in the bottom?  thanks a lot.

SERV_MTH FITM PAIDSERV
201905 97009 4
201905 97010 21
201905 97011 51
201906 97009 17
201906 97010 67
201906 97011 157

After transpose:

SERV_MTH  97009  97010  97011
201905 4 21 51
201906 17 67 157

How can I add Total as :

SERV_MTH  97009  97010  97011
201905 4 21 51
201906 17 67 157

Total     21 88 208

[Reeza]

Whats your final purpose? Putting it into a report, excel, pdf, data set? Usually totals are only added at the final reporting stage.

[pensarchem]

yes, in the end, ODS to excel.

[Reeza]

I would suggest PROC PRINT with SUM statement or using PROC TABULATE or REPORT on your original data set and skipping the transpose entirely. The results can be piped directly to Excel.

[pensarchem]

Tried a few times, got stuck. Could you share some lights?

[Reeza]

Sure, show what you tried and I'll be happy to show you what/where you need to change things 🙂

[Cynthia_sas]

Hi:

  I agree with Reeza on the recommendation for either PROC TABULATE or PROC REPORT. Given the data structure that you show, PROC PRINT will not make columns for the FIT_M variable values, as you show. But it is easily done with PROC TABULATE or PROC REPORT. There have been many previous forum postings on this. I do recommend showing the code that you've tried and explaining whether you would use ODS CSV, ODS TAGSETS.EXCELXP or ODS EXCEL to get the output report from SAS into Excel.

Cynthia

[pensarchem]

Thanks Reeza and Cynthia for helping out.

 In the beginning, I used the dumb method. 

1, proc sql,  create table to  sum for the data,

2, change the format of serv_mth to CHAR;

3, proc sql  UNION two tables together.

In the end, I used the better way, because we always need to export the file as exel.

In ODS, I directly use, (I did change serv_mth from NUM to CHAR)

rbreak after / summarize style={foreground=#000000 background=#C0C0C0 font_weight=bold};

compute after;

calyear = 'Total';

endcomp;

The reason I did not use proc report or tabulate is they always give me extra cells, but I don't know how to delete them.  We do have strict requirement of formatting.

[Cynthia_sas]

Hi:

  I don't know what you mean by "extra cells". You have a lot of control over both TABULATE and REPORT. Without data or code, it is hard for anyone to really understand what you mean.

Cynthia

[pensarchem]

data serv1;

input SERV_MTH FITM PAIDSERV;

datalines;

201905 97009 4

201905 97010 21

201905 97011 51

201906 97009 17

201906 97010 67

201906 97011 157

run;

proc transpose data=serv1 prefix=FITM out=serv2 (drop=_name_) ;

by serv_mth;

var paidserv;

id fitm;

run;

/*Note: desired result

SERV_MTH FITM97009 FITM97010 FITM97011

201905 4 21 51

201906 17 67 157

Total 21 88 208

*/

　

*Wrong code;

proc tabulate data=serv1 out=serv3;

class serv_mth fitm;

var paidserv;

table serv_mth,fitm ;

run;

Hi Cynthia, the above are the codes. thanks.

[Reeza]

Changing a date to a character is usually a bad idea, nothing sorts correctly anymore.
You never asked for the total in the TABLE statement.

table (serv_mth all="Total"), fitm;