topic Re: How to create Excel report of a dataset with summation of variable y? in SAS Procedures

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

George_S — Mon, 16 Apr 2012 19:37:02 GMT

data have;

input x y;

cards;

1 2

1 1

5 2

0 3

3 1

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

0 1

6 5

1 0

4 2

1 4

0 1

Thanks

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

Ksharp — Tue, 17 Apr 2012 02:32:44 GMT

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

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

George_S — Tue, 17 Apr 2012 13:07:37 GMT

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!

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

Cynthia_sas — Tue, 17 Apr 2012 13:42:42 GMT

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

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

Ksharp — Fri, 20 Apr 2012 03:00:50 GMT

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