topic Re: Import a blank spreadsheet column as an interval variable in SAS Enterprise Miner in SAS Programming

Import a blank spreadsheet column as an interval variable in SAS Enterprise Miner

Keigo — Thu, 21 Apr 2022 04:14:58 GMT

I used SAS Enterprise Miner to build a prediction model and now am applying it to score a new excel data where the numerical target variable is an empty column. I believe that the empty values cause the target variable to be recognized as categorical in the score data and I cannot select 'interval' as the role. How can I use the model to predict the numerical target variable in the score data? I already tried to change blanks to '.' in the excel file for SAS to take this column as numerical but it doesn't work.

Re: Import a blank spreadsheet column as an interval variable in SAS Enterprise Miner

Kurt_Bremser — Thu, 21 Apr 2022 05:16:14 GMT

A variable that has only missing values is statistically useless, anyway.

Re: Import a blank spreadsheet column as an interval variable in SAS Enterprise Miner

Kurt_Bremser — Thu, 21 Apr 2022 05:17:40 GMT

Fill the column with zeroes if you want to overwrite it.

Re: Import a blank spreadsheet column as an interval variable in SAS Enterprise Miner

ballardw — Thu, 21 Apr 2022 14:57:23 GMT

Since . would be a CHARACTER in EXCEL I doubt that would help.

Importing Excel directly means that you are at the mercy of the engines used to read from Excel. Excel has no concept of "variable" or column type per se. If the cells contain numbers, such as 0 you might be okay.

Or do some manipulation of the data set after import to create a numeric variable. If many of the character values are supposed to be numeric you might try a data step similar to:

data want;
   set have;
   newvar = input(problemvar, 32.);
run;

and see if using Newvar instead of the existing variable "works".

If you search or even just scan this forum you will see that problems with data starting in Excel, or going to Excel, is an extremely common problem.

Re: Import a blank spreadsheet column as an interval variable in SAS Enterprise Miner

Keigo — Thu, 21 Apr 2022 22:20:46 GMT

Thanks for the response. The variable with all missing values is not used to build the model. Instead, this empty target variable is in a score data where the model is used for prediction.

Re: Import a blank spreadsheet column as an interval variable in SAS Enterprise Miner

Keigo — Thu, 21 Apr 2022 22:51:05 GMT

Thank you for the code and I believe it will work. But I think there should be a solution without using SAS code in SAS Enterprise Miner.

SAS enterprise miner has a score node that is used to connect model and score data for prediction purpose. It is a common procedure to score new data in SAS enterprise miner so it is very hard to believe no setting can be used to recognize the numerical target variable.

Re: Import a blank spreadsheet column as an interval variable in SAS Enterprise Miner

Kurt_Bremser — Fri, 22 Apr 2022 05:47:35 GMT

@Keigo wrote:

Thanks for the response. The variable with all missing values is not used to build the model. Instead, this empty target variable is in a score data where the model is used for prediction.

So it will be overwritten anyway, which means it can have any numeric value.

Using a file format (csv) where you have control over variable attributes is also an option.