topic Re: Transpose and sum in SAS Programming

Transpose and sum

pensarchem — Thu, 04 Jul 2019 20:10:15 GMT

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

Re: Transpose and sum

Reeza — Thu, 04 Jul 2019 20:11:33 GMT

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

Re: Transpose and sum

pensarchem — Thu, 04 Jul 2019 20:12:41 GMT

yes, in the end, ODS to excel.

Re: Transpose and sum

Reeza — Thu, 04 Jul 2019 20:13:47 GMT

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.

Re: Transpose and sum

pensarchem — Thu, 04 Jul 2019 20:42:02 GMT

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

Re: Transpose and sum

Reeza — Thu, 04 Jul 2019 21:01:33 GMT

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

Re: Transpose and sum

Cynthia_sas — Thu, 04 Jul 2019 22:40:20 GMT

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

Re: Transpose and sum

pensarchem — Thu, 11 Jul 2019 15:45:24 GMT

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.

Re: Transpose and sum

Cynthia_sas — Thu, 11 Jul 2019 19:08:56 GMT

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

Re: Transpose and sum

pensarchem — Thu, 11 Jul 2019 22:19:37 GMT

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.

Re: Transpose and sum

Reeza — Thu, 11 Jul 2019 22:40:20 GMT

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;