use of SYLK tagset

[frisco]

Hi, I am attempting to use the SYLK tagset available on the download page. However, when i attempt to open the file using Microsoft Excel, I only get what looks like a series of commands (?!). Any help would be greatly appreciated.

[Cynthia_sas]

Hi:
Interesting. When I run the tagset code from this site:
http://support.sas.com/rnd/base/ods/odsmarkup/sylk.txt

I see the same behavior in Excel (sylk tags). I believe that's because the tagset template at the above site has a Content-type: instruction that is not needed for a local machine environment (usually you only need Content-type and/or Content-disposition if you are delivering content via a web server.)

If you alter the tagset code -- change ONLY the Doc event as shown below:
[pre]
define event doc;
start:
/*
put 'Content-type: application/vnd.ms-excel' nl;
put 'Content-disposition: attachment;filename=rfpcount.sylk' nl nl;
put 'Content-disposition: attachment;inline' nl nl;
*/
put 'ID;PSAS8' nl;
put 'P;F' nl;
put 'P;F;SB' nl;
finish:
put 'E' nl;
end;

[/pre]

Which comments out the 3 PUT statements for Content-type and Content- disposition; and store the modified tagset in SASUSER.TEMPLAT and then run this step:
[pre]

ods tagsets.sylk file='c:\temp \sylk_rept.xls' ;
proc report data=sashelp.shoes nowd;
column region subsidiary sales;
define region /group;
define subsidiary /group;
define sales / sum;
rbreak after /summarize;
title 'my title';
run;
ods tagsets.sylk close;
[/pre]

I notice that the SAS Results Viewer shows me the XML icon and won't launch Excel, even if I give the file an extension of .XLS. However, if I go over to Windows Explorer and open the file from within Explorer, then Excel 2007 correctly reads the SYLK markup tags (without Content-type, etc).

cynthia

[JackHamilton]

When you double-click on an XML file created by ExcelXP, Excel isn't opened directly. Instead, the XML file is opned by another program, MSOXMLED, which scans the file looking for more specific information on what to do next, such as "". If it finds that line, it passes the file name to Excel; if it finds another program name, it uses that; and if it doesn't find anything that can help it decide what program to use, it opens the XML file for viewing in Internet Explorer.

A SYLK file, on the other hand, is opened directly by Excel. It's a very old file format.

If I run the code in your example and navigate to c:\temp in the SAS Explorer, I see an Excel icon for the file, and double-clicking on it opens Excel. I'm not sure what you're doing when you say that the SAS Results Viewer shows you an XML icon. Perhaps if you went into the SAS registry editor and set up an entry for the SYLK filetype, SAS would handle it correctly. I don't know.

That tagset was written to address a specific need, and as a starting point to learn about tagsets. I was unable to find documentation explaining all the options and inter-relations between the different parts of a SYLK file, so the results are very rudimentary. Now that XML is available, SYLK is probably not the best choice for getting data into Excel (unless, of course, you're still using Excel 5 or below).

[Cynthia_sas]

Jack:
You're right, I don't have SYLK registered to Excel, so that probably is why I got the XML icon in the Results Viewer. And the only time I use SYLK or show it is when someone asks about it. It is an older format and I have never written reports with it. So given that, I probably won't change the SAS registry, since I can live with just double clicking on the file in Explorer.

However, if the Content-type and Content-dispostion lines are take out of the tagset template, then Excel opens the SYLK file just fine when I double click. With the Content-type and Content-disposition lines in the file, then my Excel 2007 was not happy with the ODS file -- it opened the file on a double click, but showed the SYLK tags as text, not in spreadsheet view.

cynthia

[JackHamilton]

My guess (an informed guess, but still a guess) is that Excel switches to "this is a text file" behavior when it encounters a line that's not defined by the SYLK specification. I occasionally see the same behavior with XML files if somethings not quite right.

The Content-Type and Content-Disposition are there because I was creating these files in SAS/Intrnet.