BookmarkSubscribeRSS Feed
SAS Employee

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*/ 
proc sql;  
	update data.cars_update t1 					/* Update our existing dataset*/ 
		set cylinders = (select cylinders  
			from work.excel_table  
				where = id)  
				where id in (select id 			/*Update only rows that are changed*/  
					from work.excel_table);  
One SQL update explaned here: 

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
Label: instream



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. 

Register now!

Discussion stats
  • 0 replies
  • 1 in conversation