topic Re: SAS Enterprise Guide and ODS Help (Multiple data on same Excel sheet) in SAS Programming

SAS Enterprise Guide and ODS Help (Multiple data on same Excel sheet)

IgawaKei29 — Fri, 31 May 2019 23:39:46 GMT

So I have been looking at this issue over and over and I am still not 100% sure how to get this to work. I have 7 data sets that I need to place on one Excel sheet (sorta like a template). I understand with ODS how to put these on separate sheets, but not sure how to put these all on one spreadsheet.

Thanks for any guidance on this.

Re: SAS Enterprise Guide and ODS Help (Multiple data on same Excel sheet)

Cynthia_sas — Sat, 01 Jun 2019 00:38:15 GMT

Hi:
Look for previous postings about the use of ODS EXCEL and the (sheet_interval='none') suboption.

Here's a quick example using just a few rows out of 3 different datasets.

ods excel file='c:\temp\all_together_now.xlsx'
    options(sheet_interval='none');

  proc print data=sashelp.class(obs=3);
  run;

  proc print data=sashelp.cars(obs=3);
    var make model type enginesize msrp invoice;
  run;

  proc print data=sashelp.shoes(obs=3);
    var region subsidiary product sales inventory returns;
  run;

ods excel close;

Hope this helps,
Cynthia

Re: SAS Enterprise Guide and ODS Help (Multiple data on same Excel sheet)

IgawaKei29 — Sat, 01 Jun 2019 00:51:27 GMT

Thank you Cynthia, that sheet_interval=none was the piece I was missing. Do you know if it is possible to direct SAS where to place the data on the sheet? That was also something I saw a few articles about, but was confused how to implement.

Appreciate your help on this, now I can be more SAS dangerous 🙂

Re: SAS Enterprise Guide and ODS Help (Multiple data on same Excel sheet)

Cynthia_sas — Sat, 01 Jun 2019 01:47:27 GMT

Hi, you'll have to search in the ODS EXCEL documentation. I believe there is a "start_at" suboption, but it applies to where the WHOLE sheet should start, you can't place one value in column B row 3 and another value in column D row 5, if that's what you're thinking of.

Cynthia