Did you know that you can edit SAS data through Excel if you have Add-in for MS Office (AMO) installed? You can edit any data that is defined in SAS metadata and where you have sufficient rights to do so.
If your data is big and you need to find the right rows just to make few changes you can filter and sort the data, but then you loose your ability to make changes into the data!
With this handy Stored Process example and few lines of code you can use easy filtering tools and make the changes you need.
In this video you can see
How to edit any data through AMO capabilities
When it is not working while filtering
Use of simple Stored process to make the update
How to Stored Process is defined and the SAS Code
Challenge: I wanted to keep this as simple as possible. Make your own suggestion how to make the code better! I am expecting to see at least suggestions for feedback to end user that the update was successful or error code it it's not.
Here is the SAS code (SAS 9.4M6) and don't forget to post your enhancements here for everybody to see and use !
libname instream XML; /* Definition to get the data from Excel */
libname data "C:\data"; /* Library for data to be changed */
proc copy in=instream out=work; /*Copy XML stream info SAS Dataset for later use*/
update data.cars_update t1 /* Update our existing dataset*/
set cylinders = (select cylinders
where t1.id = id)
where id in (select id /*Update only rows that are changed*/
One SQL update explaned here: https://communities.sas.com/t5/SAS-Procedures/update-statement-inside-a-proc-sql/td-p/47964
Notes for Stored Process definition:
Execution Options -> Result capabilities = Streaming
Data Sources and Targets -> Data Sources (input streams to a stored process)->
Form of Data: XML based data
Allow rewinding stream: check
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.