BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Kodit
Fluorite | Level 6

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.

Skärmklipp.PNG

 

 

 

 

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Kodit
Fluorite | Level 6

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. 

View solution in original post

5 REPLIES 5
AllanBowe
Barite | Level 11

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

 

/Allan
SAS Challenges - SASensei
MacroCore library for app developers
SAS networking events (BeLux, Germany, UK&I)

Data Workflows, Data Contracts, Data Lineage, Drag & drop excel EUCs to SAS 9 & Viya - Data Controller
DevOps and AppDev on SAS 9 / Viya / Base SAS - SASjs
Criptic
Lapis Lazuli | Level 10

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

AllanBowe
Barite | Level 11

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

/Allan
SAS Challenges - SASensei
MacroCore library for app developers
SAS networking events (BeLux, Germany, UK&I)

Data Workflows, Data Contracts, Data Lineage, Drag & drop excel EUCs to SAS 9 & Viya - Data Controller
DevOps and AppDev on SAS 9 / Viya / Base SAS - SASjs
AllanBowe
Barite | Level 11

@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

/Allan
SAS Challenges - SASensei
MacroCore library for app developers
SAS networking events (BeLux, Germany, UK&I)

Data Workflows, Data Contracts, Data Lineage, Drag & drop excel EUCs to SAS 9 & Viya - Data Controller
DevOps and AppDev on SAS 9 / Viya / Base SAS - SASjs
Kodit
Fluorite | Level 6

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. 

SAS Innovate 2025: Call for Content

Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!

Submit your idea!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 1929 views
  • 3 likes
  • 3 in conversation