"Alter Table" via SAS Excel?

[Matthijs]

Question: Is it possible for an end-user to alter a table via SAS Excel? (given the right permissions of course)

Context

Our current budgeting/forecasting-procces is done via Excel. Our Financial Controllers have built a file for our Sales-Department. Through multiple formula’s the input of Sales is ‘transformed’ into an expected return. The big issue for our Controllers is:

• A lot of work filling actual results
• ERROR (Sales alters the file and de formula’s do not work anymore)

So they asked us à Can you automate the model AND use the most actual results.

We would like the following

• Sales ‘opens’ a SAS table via Stored Procedure in SAS Excel
• Sales alters the records
• Sales updates the table

(Then another process is started which generates a second SAS-table where actual results from DWH are used, but that's already covered)

Normally we would consider an Excel-sheet and import it. But it’s a ‘must’ that multiple users can access and alter the data at the same time.

Any thoughts or suggestions?

[Kurt_Bremser]

Why use Excel at all?

Build them a nice web-interface via http://your_sas_server/SASStoredProcess/

To update datasets concurrently, these must either be present in a RDBMS or served by SAS/SHARE. Or you build the interface in a way that does the change(s) in some kind of quick batch mode once the entry is complete.

[Matthijs]

Kurt, thank you for the tip!

I'm not familiar with the SAS Stored Proces Web Application, so I used google :-). I can see how I can use it to run a stored proces.

So I can see how the end-user can 'request' the required data via a stored process with parameters. However, I couldn't find how one could then alter the table. That's the tricky part.

I'll keep on reading and searching. A suggestion is always welcome.

[DaveBirch]

Note that SAS/SHARE is a standard component of SAS Enterprise Business Intelligence (EBI), though mainly transparent to most users.

[RW9]

Its really not straight-forward.  The reason, SAS is a structured tabular storage medium, which means columns are fixed, data can only be that type, and that length, tables can only contain columns.  Excel is quite different, even though in some respects it looks similar, as each cell is its own entity.  For instance, say I write some code where I put 234 into cell "A2".  Is this correct, what is cell A2?  If it contained the word Total before it would seem to be incorrect for example, what if there is a picture there, or the format of that cell is date?  There is very little control over the Excel format, and its one of the main reasons programmers tear their hair out when people use it.  

Now its not impossible to send commands to Excel - there is DDE, which is very old and may not be supported and may not work on all setups, but you can pass explicit commands like set cell A2 to 234 through.  However, if the underlying Excel file changes in anyway (which is most likely), then what you do may mess up the whole thing.  In the same way, you could read the Excel file into SAS and process it and write it out, but then you lose the Excel only bits.

My first advice is always, drop Excel, it is not useful in any scenario in which its used - data transfer file formats (CSV, XML etc,) are far better for sending data, RTF, PDF are better for reviewing data, Excel fits in as the forced swiss army knife doing a bit of everything not very well.

One other suggestion I would make is that if your fixed on using Excel as a tool, then use the full functionality of that tool rather than trying to shoe-horn it into another tool.  All Office apps have VBA behind them, and its a reasonably good language, with a fair bit of integrated functionality.  Most things can be done diretly in the file and populated based on rules and if you need SAS output, then that can be saved as CSV from SAS, and your VBA code can open, process, and close that CSV file.  It does work very well, however as said Excel is not really the tool for that, and QC, Validation, maintenance and such like will be high on your priority list if you do go that way.

[Matthijs]

RW9, thank you for the input.

Both you and Kurt have the same 'statement'.... drop excel. Seeing both your reactions I can see that dropping excel is the best way to go.

However issues it a challenge. How do I get the end-user to alter the table. Basicly that's my question now after dropping excel.

[RW9]

Yes, it is very easy for us to say drop Excel, but I am aware that in certain industries Excel cannot be removed.  As I mention above, Excel does include VBA which can do most processing on Excel sheets, so if you need to calcualte numbers update things you can do it directly in the Workbook.  Maybe have a look at:

http://www.excel-vba.com/excel-vba-solutions-beginners.htm

You could also import the whole worksheet to SAS - depending on your file proc import, libname excel (all have many examples through google seach), and then make the updates in SAS, and then proc export, or libame excel, however there are limitations, and its likely you are working with a ready made workbook that export functionality may not be able to replicate.

[Kurt_Bremser]

I guess that the table in question is not very large.

My approach would be like that:

- create a HTML FORM for each row that contains the table data in input fields so changes can be made; name the SUBMIT button "Change"

- once a changed row is submitted, check the changes and run an update against the base table

- if adding rows is necessary, add a FORM with empty data where the SUBMIT button is called "Add Row"

Once an update is made, send the HTML with the current data

Since this method uses short accesses to the base table, collisions should be rare, if they happen at all.

[DaveBirch · Accepted solution]

Hi Matthijs, it's not clear what you mean by "SAS Excel" - I thought perhaps you were referring to the SAS Add-in for Microsoft Office (AMO) being run from Excel, but it's clear that the other responders weren't contemplating that at all. 

Does your site have SAS AMO?  It is always (I think) bundled with SAS Office Analytics (OA) and SAS Enterprise Business Intelligence (EBI), and optionally with some other bundles.  It is normally site licenced, rather than per user.  And, most importantly, it can do exactly what you want !  - All whilst retaining the familar look and feel for users (including cell formating without complex coding in SAS), and eliminating most needs for Excel formulas and macros.

AMO has had 'write-back' capability for some time, but only since SAS 9.3 and AMO 5.1 has it been what I consider 'production ready'.  In AMO 7.1 the SAS ribbon has an 'External Data' section with tabs for 'Begin Edit', 'Commit', 'Create New Records', and 'Delete Records' etc.. When a user clicks on 'Begin Edit' the underlying SAS dataset is locked, and the tab changes to 'End Edit' (which will release the lock).  I recommend that the first Stored Process creates a temporary table to edit, and a second Stored Process validates the altered table before applying the changes to a permanent dataset.

You would need to register the users in SAS metadata, but since it is site licenced, this won't involve extra cost.

Does this sound like a better solution?

[Matthijs]

Thank you DaveBirch.

Also thanks to the other responders and the offered sollutions!

[rogerjdeangelis]

I am not familiar with AMO for inpace excel editiong, so it may be a better solution.

If you want to update excel in place the options are limited. If the first column is a primary key you can use 'SQL' passthru and and add rows(at the end) and I believe change individual cell values. I do not think it is possible to insert a row or reorder the data using SQL passthru.

You may have to create a named range.

Microsoft Excel drives have limitations, excel is not a relational database.

I have some posts on this in SAS-L.

R/Python  are quite flexible and R can read SAS datasets. 

R/Python may have some usefull packages. For instance R/Python can create named ranges, read cell formattinng...

Anthig is possible if you load the data into SAS and create a and write a new sheet.