How to create Excel report of a dataset with summation of variable y?

[George_S]

data have;

input x y;

cards;

1         2

1         1

5         2

0         3

3         1

1         3

1         3

0         1

6         5

1        0

4        2

1        4

0        1

;

wanted:(excel file,,use proc report ,with summation of y,  sum=28)

x        y

1         2

1         1

5         2

0         3

3         1

1         3

1         3

0         1

6         5

1        0

4        2

1        4

0        1

          28

Thanks

[Ksharp]

EASY.

data have;
input x y;
cards;
1         2
1         1
5         2
0         3
3         1
1         3
1         3
0         1
6         5
1        0
4        2
1        4
0        1
;
run;
ods listing close;
ods tagsets.excelxp file='c:\total.xls' style=sasweb;
proc report data=have nowd;
column x y;
define x/display;
define y/analysis sum;
rbreak after/summarize;
run;
ods tagsets.excelxp close;
ods listing;

Ksharp

Message was edited by: xia keshan

[George_S]

Thank you Ksharp!

One more question is how to change the style to make the cells looks like the regular excel cells? That is remove the cells filling and border(set no border,no fill in excel).

Thanks!

[Cynthia_sas]

Hi:

  Chiming in with my .02...when you use ODS TAGSETS.EXCELXP and ODS HTML, the assumption is that you WANT the style to be used. You can try to change to STYLE=MINIMAL on your ODS statement. However, if you want NONE of the formatting that comes with these two ODS destinations, then you can try ODS CSV as your destination, which creates a comma-delimited file -- that will open in Excel and look like a "regular" Excel file -- as though you had just typed the numbers into the cells.

cynthia

[Ksharp]

There is a workaround way. If you don't mind.

data have;
input x y;
cards;
1         2
1         1
5         2
0         3
3         1
1         3
1         3
0         1
6         5
1        0
4        2
1        4
0        1
;
run;
ods listing close;
proc report data=have nowd out=want(drop=_:);
column x y;
define x/display;
define y/analysis sum;
rbreak after/summarize;
run;
ods listing;
proc export data=want outfile='c:\want.xls' dbms=excel replace;run;

Ksharp