topic Re: excel specify each report the start and end point in SAS Programming

excel specify each report the start and end point

radha009 — Fri, 28 Sep 2018 13:19:02 GMT

In ODS excel , i have multiple (6 tables) proc reports display in one sheet. Is there a way i define for each proc report the start and end row on the excel sheet.

Re: excel specify each report the start and end point

RW9 — Fri, 28 Sep 2018 13:31:12 GMT

Show some example please. What is your code, what is the output, what is wrong?

I will take a guess at:

proc report...;
...

  compute after;
    line "End of report";
  endcomp;
run;

Re: excel specify each report the start and end point

VDD — Fri, 28 Sep 2018 13:45:37 GMT

I use a macro named sheetf that I assign in my output which places those data I want onto the sheet I want into the workbook

"Excel|&temppath.\[&workbook.]&sheetf.!r1c1:r28c10" notab;

Re: excel specify each report the start and end point

Reeza — Fri, 28 Sep 2018 15:39:31 GMT

That's DDE code but you said you're using ODS EXCEL?

@VDD wrote:

I use a macro named sheetf that I assign in my output which places those data I want onto the sheet I want into the workbook

"Excel|&temppath.\[&workbook.]&sheetf.!r1c1:r28c10" notab;

Re: excel specify each report the start and end point

radha009 — Fri, 28 Sep 2018 16:31:33 GMT

my code below, i didn;t get how to use within the report the range of report display. i know in excel options we have start_at.

ods excel options(sheet_name='Fruits' embedded_titles="on" sheet_interval='none');
title "All Incidents - Fruits by Group";
proc report data=dash.Ent_AllInc_Final;
run;

title "All test Incidents - Friuts by Group";
proc report data=dash.Fru_L2Inc_Final;
run;
title "Data to Create the Pie to Bar charts for Fruits";
proc report data=dash.Fru_pie_Final;
run;
title "Managed INC by Top Application";
proc report data=dash.Fru_Top_apps;
run;
title "Requests Supported by Support";
proc report data=dash.Fru_change_req;
run;
title "Fruits -Major Incident Report";
proc report data=dash.Fru_Major_INC;
run;

Re: excel specify each report the start and end point

radha009 — Fri, 28 Sep 2018 16:32:22 GMT

yes, i am using OD excel.

Re: excel specify each report the start and end point

Reeza — Fri, 28 Sep 2018 16:38:52 GMT

Start at only applies to the first table.
Beyond that I'm not sure how to get data to specific locations.

The method I've used isn't great. I create it manually in Excel first, with ranges and then export data to the template and specific ranges.

AFAIK, ODS EXCEL doesn't yet support exporting to specific locations.

ODS TAGSETS.EXCELXP had some options (SKIP_SPACE) that may have helped but I'm not sure if they're in ODS EXCEL.

@radha009 wrote:

my code below, i didn;t get how to use within the report the range of report display. i know in excel options we have start_at.

ods excel options(sheet_name='Fruits' embedded_titles="on" sheet_interval='none');
title "All Incidents - Fruits by Group";
proc report data=dash.Ent_AllInc_Final;
run;

title "All test Incidents - Friuts by Group";
proc report data=dash.Fru_L2Inc_Final;
run;
title "Data to Create the Pie to Bar charts for Fruits";
proc report data=dash.Fru_pie_Final;
run;
title "Managed INC by Top Application";
proc report data=dash.Fru_Top_apps;
run;
title "Requests Supported by Support";
proc report data=dash.Fru_change_req;
run;
title "Fruits -Major Incident Report";
proc report data=dash.Fru_Major_INC;
run;

Re: excel specify each report the start and end point

radha009 — Fri, 28 Sep 2018 16:43:34 GMT

Thank you. Its kind of a workaround until i found the right solution.