BookmarkSubscribeRSS Feed
sathya66
Barite | Level 11

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,

 

SS

3 REPLIES 3
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

sathya66
Barite | Level 11

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.

 

 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

"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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 909 views
  • 0 likes
  • 2 in conversation