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*/
run;
proc sql;
update data.cars_update t1 /* Update our existing dataset*/
set cylinders = (select cylinders
from work.excel_table
where t1.id = id)
where id in (select id /*Update only rows that are changed*/
from work.excel_table);
quit;
/*
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
Fileref:instream
Allow rewinding stream: check
Label: instream
*/
