Learning SAS? Welcome to the exclusive online community for all SAS learners.

In SAS studio, after Excel file import, SAS defines continuous (numeric) variables as character variables. How can I change this?

Reply
New User
Posts: 1

In SAS studio, after Excel file import, SAS defines continuous (numeric) variables as character variables. How can I change this?

hello,

I was looking for two days for an answer to this presumable easy problem but could not find it anywhere...

So here is my problem:

I use SAS studio University edition with VMware fusion on my Mac. I imported successfully and xlsx file but when I started to do some basic descriptive analyses (mean, median, SD) of the continuous variables in the columns, I realized that SAS gave my continuous variables a Char type (instead of numeric). Thus, I cannot go ahead with my analysis.

Is this because the length of the variable is 2 instead of 8? If yes, how do I change?

Or is this because my format/informat is set to $1 instead of  Best 12? If yes, how do I change?

Thank you very much for some help on this,

Max

Super User
Posts: 10,526

Re: In SAS studio, after Excel file import, SAS defines continuous (numeric) variables as character variables. How can I change this?

The reason is that the Excel engine told SAS that the variables were text based on the first few rows of data.

The only real consistent way to control data types from Excel is to export them to csv and use a program you write to read the csv file as needed. The  options for importing CSV files include a guessingrows option to tell SAS how many rows to examine to decide text or character.

In Base SAS a proc import for a CSV file creates a program that is visible in the log that you can copy and modify to change informats, lengths, formats and to assign labels (highly recommended if you are going to do this very often with the same layout).

There is an option by changing a Windows registry setting to expose more rows for the decision on whether a variable is text or not but that just delays having the same issue elsewhere. And likely to get changed with Microsoft updates, or a new PC.

A third option is to ensure the first few rows of your data do not contain any blank or non-numeric looking values before importing. Which may be problematic for some data sources.

Fourth, live with the data as imported and use a datastep to create the numeric values. The program stub below reads the imported data and creates a numeric value for VAR1 that was initial imported as text.

data want;

     set have;

     NumVar1 = input(var1, best12.);

run;

Ask a Question
Discussion stats
  • 1 reply
  • 613 views
  • 0 likes
  • 2 in conversation