Stored Process to open data in browser, allow the user to edit it and return then data

[Kodit]

Hi, 

I'm trying to build a browser based interface where the user can update rows in a table using a SAS Stored Process.

So far I've been able to get the process to pick out the right row and print it in the browser using the following code;

&akt and &ref har variables telling which table and which row to pick.

%STPBEGIN;

%macro plock;

data tempplock;

set t_stage.&akt.;

%if "&akt."="produkter" %then %do;

where cat(compress(produkt),compress(kund))="&ref.";

%end;

run;

%mend plock;

%plock;

ods html5;

proc print data=tempplock noobs;

run;

ods html5 Close;

%STPEND;

which generates this table in the browser.

Now my question is; how do I go about making the user be able to change the values and return these to SAS?

[AllanBowe]

@Kodit - GREAT question!

A Stored Process Web Application is the perfect choice for letting users make changes to data, for reasons such as the following:

 - Updates are made using a system account (eg sassrv) so the end user doesn't need to have write access to sensitive data

 - You can apply validation at point of data entry

 - It's much easier for users / developers alike

The very first piece of advice I have for you is to separate your frontend web (web application logic) from backend SAS (data access and manipulation logic).   For this you can use the open source Boemska h54s adapter.  I have written two guides to building web apps this way - a short one and a more detailed one.  Boemska even have a free application (the HOT editor) on github which may serve your initial requirements - https://github.com/Boemska/sas-hot-editor

Note that the tool above will only work with SAS datasets, and does a complete replace (destroying indexes etc) of the backend table each time. 

For a complete solution, there are many more considerations.  Such as:

 - handling of numerical precision and special characters

 - dynamically handling different data types (strings / numerics / dates / datetimes)

 - loading target tables of different types (retained keys, composite keys, scd2, bitemporal)

You will also need to work with a database, or SAS Share (rather than SAS datasets) to enable concurrent access from multiple users.  

If you'd like to truly delight your users / developers / admins (and save yourself several years of development and testing, as well as avoiding a custom build), I'd urge you to consider the Macro People Data Controller.  This is a Stored Process Web App with the following features:

 - Secure, Real Time data uploads 

 - All changes routed through an approval mechanism (approver signs off changes and then they are applied)

 - Full audit history (what changed, when, by whom with reason capture)

 - Alerts

 - Documentation (user guide, admin guide)

 - Secured with SAS Metadata

 - Extensively battle tested

PM for a no obligation demo!

[Criptic]

@AllanBowe - quick question: Where can I find out more about the Macro People Data Governor you mention in your response?

[AllanBowe]

@Criptic- I'm in the process of migrating the documentation onto Github pages. 

But the best way is to send me a PM so I can 'live demo' the tool directly!

[AllanBowe]

@Criptic / @Kodit - the site which explains the data editing tool is now live, and available at https://datacontroller.io.

The documentation is available here:  docs.datacontroller.io

[Kodit · Accepted solution]

So I solved this by changing the ods template to output html-table in an editable table. Then I wrote a JavaScript to to create a json-string from the values in table and send it to SAS.