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

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

Kodit — Tue, 03 Apr 2018 07:44:35 GMT

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?

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

AllanBowe — Mon, 23 Apr 2018 13:37:30 GMT

@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!

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

Criptic — Tue, 03 Apr 2018 09:37:23 GMT

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

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

AllanBowe — Mon, 23 Apr 2018 13:36:54 GMT

@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!

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

AllanBowe — Tue, 11 Sep 2018 14:18:07 GMT

@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

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

Kodit — Tue, 22 May 2018 09:23:37 GMT

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.