Help using Base SAS procedures

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

Reply
Contributor
Posts: 35

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

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

;

wantedSmiley Sadexcel 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

Super User
Posts: 10,020

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

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

Contributor
Posts: 35

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

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!

SAS Super FREQ
Posts: 8,862

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

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

Super User
Posts: 10,020

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

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

Ask a Question
Discussion stats
  • 4 replies
  • 322 views
  • 3 likes
  • 3 in conversation