topic Re: Exporting Header-Only (no Observations) to Excel in ODS and Base Reporting

Exporting Header-Only (no Observations) to Excel

SB_145 — Mon, 28 Sep 2020 13:47:17 GMT

I am looking for a way to export only the header of a dataset to Excel. No matter whether I tried, from starting with an empty dataset to using a filter right before the export, SAS doesn't export the tab that contains a header only.

Any suggestions would be highly appreciated.
Thank you!

Re: Exporting Header-Only (no Observations) to Excel

SB_145 — Mon, 28 Sep 2020 13:28:36 GMT

I know it's ironic that the topic about exporting an Excel file with a header only has only a header (it was unintentional--SAS reps., we need an edit button).

Now the actual issue: I am looking for a way to export only the header of a dataset to Excel. No matter whether I tried, from starting with an empty dataset to using a filter right before the export, SAS doesn't export the tab that contains a header only.

Any suggestions would be highly appreciated.
Thank you!

Re: Exporting Header-Only (no Observations) to Excel

Tom — Mon, 28 Sep 2020 13:42:46 GMT

Do you have to use ODS? This works:

libname out xlsx 'myfile.xlsx';
data out.class;
  set sashelp.class (obs=0);
run;

Re: Exporting Header-Only (no Observations) to Excel

Tom — Mon, 28 Sep 2020 13:46:14 GMT

Click on the vertical dot-dot-dot next the message to see the edit message menu item.

Re: Exporting Header-Only (no Observations) to Excel

SB_145 — Mon, 28 Sep 2020 14:00:00 GMT

Hi Tom, Thank you for your suggestions. Unfortunately it doesn't work. I'm working on a Unix server, though I doubt this is the cause (never had problems creating actual Excel files) 1) The result of your export code is a file with the extension $$1. 2) When I try to export my own empty dataset, I do obtain an Excel file; however when I try to open it, I get a message that the file is corrupted. 3) If I try to export my data to a file with multiple tabs, the tab that should contain only with the header is not created at all. For what's worth, my header has column names down the lines of '20-DEC-2019 01'n. Could this be the cause of the problem (it's not a problem when I export at least the first observation)? Thank you again.

Re: Exporting Header-Only (no Observations) to Excel

Tom — Mon, 28 Sep 2020 14:10:02 GMT

Sounds like you tried to read the XLSX file before SAS had finished creating it. If you open the XLSX with a libname statement you need to clear the libref before it closes the file and saves it.

libname out xlsx 'myfile.xlsx';
data out.class ;
  set sashelp.class ;
  where age=0;
run;
data out.cars;
  set sashelp.cars (obs=5);
run;
libname out clear ;

Re: Exporting Header-Only (no Observations) to Excel

SB_145 — Mon, 28 Sep 2020 14:47:52 GMT

I still get a $$1 file. I even erased everything in the destination folder, closed Excel, and changed the server from which I run the code. Same exact result. Using a WHERE statement vs. an OBS statement made no difference either.

Thank you.