The SAS Output Delivery System and reporting techniques

Is there a way to reverse engineer a SAS Template from Excel worksheet

Reply
New Contributor
Posts: 3

Is there a way to reverse engineer a SAS Template from Excel worksheet

Hi,

I have a project in which we need to distribute formatted excel worksheet with data to users. ExcelXP tagset with appropriate template can be used to generate the XML ouput on Unix box where SAS production process is running. That XML can be used by windows users to see the output as an Excel worksheet. I would like to maintain the Headers/Footors, row labels/column labels in a spreadsheet on a windows machine so it is easy to maintain without having to recode Proc Template. Is there a way to generate (reverse engineer) Proc template code from an existing windows spreadsheet.

SAS Super FREQ
Posts: 8,868

Is there a way to reverse engineer a SAS Template from Excel worksheet

Posted in reply to Sampat_Saraf

Hi:

  I don't understand what you mean when you say:

"I would like to maintain the Headers/Footers, row labels/column lables in a spreadsheet on a windows machine so it is easy to maintain without having to recode PROC TEMPLATE."

  If you are talking about EXCELXP suboptions -- you can store those in an INI file and just reuse the INI file in your invocation. If you are talking about style template changes, you only have to build the style template one time and then reuse it. If you are talking about changing the tagset template for TAGSETS.EXCELXP, well, I NEVER do that until Tech Support verifies for me that modifying the tagset template XML is the ONLY way to do what I want.

  How do you envision your saved spreadsheet working??? SAS doesn't "read" any cosmetic saved info from the saved spreadsheet unless you go to a LOT of work.

  Can you elaborate?

cynthia

New Contributor
Posts: 3

Is there a way to reverse engineer a SAS Template from Excel worksheet

Posted in reply to Cynthia_sas

I was trying to see if there was an easy way to create s style with "cosmetic" saved info from a windows spreadsheet. Was trying to see if there was a quick way to do that in SAS? It is easier and more user friendly to change titles, row and column labels in spreadsheet than in a SAS style.

I did find a work around:

1. I saved the spreadsheet with fixed "cosmetic info" and formulas as xml in Excel.

2. ftped the xml file to Unix.

3. Used SAS datastep to modify the changing content programatically in this ftped xml file

This revised xml file now has fixed and cosmetic content from the worksheet I am maintaining on windows and variable content from the sas process pulling data from a database.

PROC Star
Posts: 7,492

Re: Is there a way to reverse engineer a SAS Template from Excel worksheet

Posted in reply to Sampat_Saraf

This product "claims" to have the precise functionality you are looking for: http://www.sapmaker.com/UserGuideSapMakermetadata.htm

I've never tried it, but it may be worth looking into.  If you do, please let the forum know what you discover.

Their claim:

·      Automatically create SAS program template for creating a dataset based on the metadata document (in an Excel document).


SAS Super FREQ
Posts: 8,868

Is there a way to reverse engineer a SAS Template from Excel worksheet

That link seems heavily involved in dealing with CDISC XML and reading define.xml or other CDISC XML files and creating a program "template" for creating a SAS dataset. My guess is that the program "template" is probably not PROC TEMPLATE code -- they show the creation of XML, so my guess is that they are either building an XML Map in order to read or write CDISC XML or they are building a model code program, either macro, DATA step or PROC SQL to deal with the file once the XML has been handled.

My sense is that the OP was talking mostly about style changes and liked the ability to use Excel to make cosmetic changes. I guess since EG has the Style Wizard which allows you to build a CSS file that you can import into a PROC TEMPLATE style template definition, I would think that it might be fairly easy to strip the CSS out of the XML file and then use the IMPORT statement in PROC TEMPLATE to create a style that you want.

The method that the OP describes is clever -- to save the cosmetics as XML and then write the data into the XML sheet. I find the Microsoft spec for their XML to be rather opaque, so this was not a trivial task. That's my .02 on the idea.

cynthia

PROC Star
Posts: 7,492

Is there a way to reverse engineer a SAS Template from Excel worksheet

Posted in reply to Cynthia_sas

Cynthia:  I'd still be interested in discovering what that company is offering.  Again, I linked onto the quote that I mentioned in my last post.  Yes, they may or may not be suggesting what the OP was asking.  Regardless, I agree, it would make a very nice addition for SAS to be able to accomplish such a task.

That's twice, this week alone, that two such possibilities presented themselves for SAS to easily add two very nice features to the current suite.  The other was regarding expanding the no longer documented proc spell to allow users to match text with a dictionary and identify both matches and non-matches in output datasets.

New Contributor
Posts: 3

Re: Is there a way to reverse engineer a SAS Template from Excel worksheet

Posted in reply to Cynthia_sas

I see advantage to having cosmetic and fixed content of a spreadsheet managed by business users who are familiar with excel and its formattling capability. The variable content for the final spreadsheet output is likely based on a back-end database and SAS expertise need to be engaged only when logic for computing the variable content changes.

I am findimng that the option for saving worksheet as an XML and using SAS or unix Script to substitute the variable conent works well for summary information (25-50 summary statistics). I coded the cells in XML spreadsheet as Chgvar1, Chgvar2,..., etc. Assuming that XML itself is unlikely to have these literals, I can substituite these programatically with computed values in Unix.

On the other hand, if the variable content is big or changing from run to run (say list of all customers meeting a certain criteria), the approach breaks down. If there is a better way for handling such situations, I will like to find out about it.

Ask a Question
Discussion stats
  • 6 replies
  • 278 views
  • 0 likes
  • 3 in conversation