Interact, learn and grow

Join Now
Reply
This is an open group. Sign in and click the "Join Group" button to become a group member and start posting.
Highlighted
SAS Employee
Posts: 1
Juletip #5 –Unleashing the power of Excel – and being nice to your user
[ Edited ]

Christmas is a time when you should care of each other – and why not starting by being nice to your users. This Christmas tip will help your user in the task of loading data to their system from Excel, using SAS Add-In for Microsoft Office (SAS AMO). This case uses the streaming input facility provided within SAS AMO, so you do not have to worry or care if the servers are running on Linux or UNIX. To do this, the user simply selects an area in Excel, and all the data is sent to the SAS server. All you have to do to receive the data is to correctly configure your SAS STP in the SAS Management Console, and, following that, write some small pieces of code. All the code is provided as an attachment to this Christmas tip.

 

Step one: Setting up you SAS Stored Process in SAS Management Console.

Under sheet “Data” make sure you make these configurations:

sas management console.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

In your stored process, add this line to read data sent from Excel:

libname indata xml xmlfileref=excel_s Encoding=UTF8;

 

Note 1: xmlfileref matches fileref in SAS Management Console.

Note 2: By adding encoding=UTF8 our Nordic characters will be received correctly. Encoding option is optional.

 

The data sent from Excel may now be read into a regular SAS dataset using the code below:

data input_data;
	set indata.excel_table;
run;

The table called excel_table in the libname indata now corresponds to the user selection in Excel.

 

In this manner, we have made life easier for our user by letting him/her use Excel, select data and send it to SAS. In some cases, this data will be stored permanently somewhere on the servers. Before we store data permanently, data needs to be validated. The user might potentially send thousands of rows, which may include an error on one or two, and I believe that it is nice to call attention to the specific rows where the errors can be found. This will make it much easier for the user to correct the errors.

 

This check will be implemented using the hash object and the _n_ variable to indicate on which rows we have errors on. In the following example we will load a file with employees, where only employees with a name present in sashelp.class are allowed. When there is no match, we will output that row to a no_match dataset and provide our user with a fair error message, making it possible for the user to correct the error.

 

See code below for an example:

%global validation_error;
%let validation_error = FALSE;
data valid_names(drop=rc) 
	 not_valid_names (drop=rc);
	set input_data;

	if _n_ = 1 then do;
		declare hash h1(dataset: 'sashelp.class');
		h1.defineKey('name');
		h1.defineDone();
	end;

	*************************************************************************************************;
	* Note: input data must have a column called name for this step to work. If there is no such    *;
	* column there will be an execution error in this step.                                         *;
	*************************************************************************************************;
	rc = h1.find(); 

	*************************************************************************************************;
	* If rc = 0 then the name was present in sashelp.class, if rc ne 0 there was no match           *;
	*************************************************************************************************;
	if rc = 0 then do;
		output valid_names;
	end;
	else do;
		error_on_row = _n_;
		error_message = catx(' ', name, 'is not a valid name. Please use only names in sashelp.class');
		output not_valid_names;
		* Set macro variable validation_error to true if this happens;
		call symputx('validation_error', 'TRUE', 'G');		
	end;
run;

%macro run_data_load;
	%if &validation_error = FALSE %then %do;
		* call macro or include program that load the data;
	%end;
	%else %do;
		* There was some problem validating the data - inform the user;
		title1 'Data validation errors - errors on the following rows';
		proc print data=not_valid_names label noobs;
		run;
	%end;
%mend;
%run_data_load;

Run from Excel (requires installation of SAS AMO on client computer):

Select an area:

 

area in Excel.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Select your stored process:

select stp.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Browse using Reports -> Folder structure. Select where result should go and press ok.

Result comes out that there is a data validation problem, we all know that there is no one called Santa in sashelp.class.

 

result.PNG

However, since row is presented and there is a clear error message – the user will probably correct this error by herself.

 

This streaming input facility has been implemented in customer projects. In case of further questions, please post below.

 

Complete code and spk-file for STP are attached to this note in a zip-file

Attachment