topic Re: proc reports: side-by-side in the worksheet in SAS Programming

proc reports: side-by-side in the worksheet

sathya66 — Thu, 12 Apr 2018 15:15:15 GMT

Hi All,

I want to see the two reports side by side in one excel sheet. I tried below code . It is creating excel file but when I try to open excel file ,it showing an error message saying " excel cannot open the file 'panels.xlsx' because the file format or file extension is not valid. verify that the file has not been corrupted and etc"

ods tagsets.msoffice2k_x  file="H:\TEST\panels.xlsx"
      options(panelcols="3") style=normal;

  proc print data=sashelp.prdsale(obs=10);
  var actual predict country region;
  where country="CANADA";
  title "Canada Sales";
  run;

  proc print data=sashelp.prdsale(obs=10);
  var actual predict country region;
  where country="U.S.A.";
  title "USA Sales";
  run;

  proc print data=sashelp.prdsale(obs=10);
  var actual predict country region;
  where country="GERMANY";
  title "Germany Sales";
  run;

 ods tagsets.msoffice2k_x close;

Thanks,

Re: proc reports: side-by-side in the worksheet

RW9 — Thu, 12 Apr 2018 16:04:47 GMT

ods tagsets.msoffice2k_x file="H:\TEST\panels.xlsx"

XLSX is not tagsets.msoffice2k. Name the file xls. That may be one problem that Excel is expecting an XLSX file and your not creating one, but calling it that. Also, you might want to give it a style.

Me I would use either the latest version which is tagsets.excel, or if you have an old SAS use excelxp. Not sure if these have panelling though.

Re: proc reports: side-by-side in the worksheet

sathya66 — Fri, 13 Apr 2018 09:15:27 GMT

no luck with xls.

now we are using tagsets.excelxp but no option "panelling" for this. There is an option called start_at but this option is also not working for me.

Re: proc reports: side-by-side in the worksheet

RW9 — Fri, 13 Apr 2018 09:33:41 GMT

"no luck with xls." - this does not tell me anything. I just ran the msoffice2k_x tagset, and your code, only changing the path and the filename to xls, and it works fine for me. It does give a warning that the file format is not as expected, this is true as the file is actually a html page not an Excel file, but it is in a form that Excel can read, just click Yes to keep opening it.

Excel is a really bad format for any type of endeavor, and getting data out to it is a real pain. ods excel can create native xlsx files, and proc export can create native xls files, but all tagsets generate html or xml. I would look at your process, do you really need to use Excel, and do you need to use it in that way? Avoid it as much as possible.

The one other way is to create an Excel read macro, so you dump data out from SAS, then run a macro in Excel to read in the data and process as you want. Anyways, the tagset works.