BookmarkSubscribeRSS Feed
PietariKoskela
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*/ 
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
*/

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

Discussion stats
  • 0 replies
  • 467 views
  • 7 likes
  • 1 in conversation