Modifying a SAS table outside a SAS client

[ger15xxhcker]

Hello!

I want to solve a problem that I want to modify my SAS tables outside of the base SAS client. What do you think could be the best solution?

So the lineup would be:
Export base from SAS - say an XML that contains table metadata (length, format, label, key, index, etc.) - I modify this XML with an external program (.net or python). Then I would re-import this xmlt, overwriting the original table.

Does this work? I have seen there are web solutions as well. If you have any ideas that could be the easiest way to solve this, please help.

In summary, modifying the SAS table outside the base SAS client. Export and import can go from base SAS.

Thank you very much!

[ballardw]

Most of what you list would properly be the job of Proc Datasets to modify a data set in place which is going to be much more efficient in terms of processing time or disk space.

Outside of SAS? Don't know of any tools currently that modify SAS data set metadata.

Anything involving "export/import" would probably completely replace the data set including the data and have all of the associated performance issues of moving data around.

[Reeza]

Do you have Python and the saspy package set up so you can directly interact with your SAS server and data sets using Python?

What are you trying to accomplish here? What business/workflow problem are you trying to solve?

[ger15xxhcker]

I would have to solve the business problem of allowing users outside of SAS to modify data in an existing table. I want to solve the modification procedure in either python or .net. Export and import by calling a .bat file in SAS. I will listen to any solution.

I have also seen a solution where a H54S-based HTML5 Web Application for the SAS® Intelligence Platorm™ that lets end users easily edit, create and upload designated SAS datasets using a familiar spreadsheet-like interface.

[Reeza]

Cloud services an option? Write your table to an Airtable, have the users do whatever is needed and read it back in.

[ger15xxhcker]

To tell you the truth, I don’t know this technology. Can you explain it or could you give an example? Maybe a reference to this procedure? Thanks!

[Reeza]

AirTable is a Software as a Service Platform that helps deal with workflow issues..like this 🙂
https://airtable.com

[SASKiwi]

Another option worth considering would be the SAS Add-In to MS Office. With this installed, from Excel you can extract a SAS dataset into a sheet, make modifications, then save the SAS dataset over the original version. You do need to be able to connect to a SAS 9 server installation though. 

[ger15xxhcker]

Unfortunately, excel is not good because the tables are too big and the partner does not have this license. But thank you for your idea!

[ballardw]

---

@ger15xxhcker wrote:
Unfortunately, excel is not good because the tables are too big and the partner does not have this license. But thank you for your idea!

---

"Tables too big" really points to something that does not involve "export and import" of data.

Since for most practical purposes Labels and formats aren't much use outside of SAS I really think this points to an "in SAS" solution like Proc Datasets or other metadata management.

An external source containing the needed modifications could be read into a SAS data set and then use code that appears on the this forum in many places to use that data set to drive Proc Datasets code with Call Execute might be a viable approach.

[ger15xxhcker]

Yes you are right. I'm looking for something like this: https://github.com/Boemska/sas-hot-editor

[Reeza]

That's open sourced under the MIT license so why not just use it?

[AllanBowe · Accepted solution]

We have built a tool that does exactly this, and it's free for up to 5 users.

You can only modify _existing_ tables, and - unlike the open source hot editor - it will NOT replace the target table, but perform updates / appends according to the load type (eg SCD2, UPDATE, BITEMPORAL). This way the data MODEL is protected, and the data VALUES can be safely modified.

There is a seperation between the edit and the approval, there are data quality validations, and you can even drag & drop excel files into the browser.  

The tool was nominated for the 'excellence in innovation' award this year, and we have several reference customers - see https://datacontroller.io/blog

To see it in action, there are some videos here:  https://docs.datacontroller.io/videos/

If you'd like a copy, just get in touch with me either here or using the contact form:  https://datacontroller.io/contact/