topic Re: Make ods Excel ouput as table automatically in ODS and Base Reporting

Make ods Excel ouput as table automatically

songsujing — Mon, 28 Feb 2022 18:20:46 GMT

I used proc report and ods excel destination to generate excel file, but I would like to make the data in excel to be table , not list . what I mean in excel when you chose data area and click control + T the data area will convert into a table. Is there ods excel option to do this automatically or other ways to do this in SAS?

Thanks a lot

Re: Make ods Excel ouput as table automatically

ballardw — Mon, 28 Feb 2022 19:17:10 GMT

Suggest show the code you use for creating your output. Include the ODS destination statements as well as the procedure.

That we it will at least be possible to tell if you are not using options that make this impossible.

Re: Make ods Excel ouput as table automatically

songsujing — Mon, 28 Feb 2022 20:16:45 GMT

data test;

input a b c ;

card;

1 2 3

4 5 6

;

run

ods excel file = "desktop/test.xlsx" options(sheet_name = "Test" autofilter="All");

proc report data = test missing;

column a b c;

define a / display 'A' left ;

define b / display 'B' left ;

define a / display 'C' left ;

run;

ods excel close;

in the excel file sheet test, for data range a1: c3, I like to convert it to table in excel. How do I do it by SAS programming instead of I manually go to the excel, choose data range and press ctrl+T to convert it to table?

Thanks a lot

Re: Make ods Excel ouput as table automatically

Sajid01 — Sun, 03 Apr 2022 14:05:41 GMT

Tables in excel are essentially named range of cells with header and data. It is essentially an excel feature.
SAS documentation does not list it. https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/odsug/p09n5pw9ol0897n1qe04zeur27rv.htm)