Reading column as numeric (SAS is guessing character)

Reply
New Contributor
Posts: 3

Reading column as numeric (SAS is guessing character)

Hello All,

 

I'm importing a CSV file that has hundres of millions of rows> For one column, the first tens of millions are empty, and I think SAS is guessing CHARACTER instead of numeric. I havent found yet how to define a numeric type when importing from CSV. I know that I can put a $ if i wanted to define it as character,

 

 

thank you

SAS Employee
Posts: 1

Re: Reading column as numeric (SAS is guessing character)

Hi,

You say that you think SAS is guessing character instead of numeric, what makes you say that, is that because of an error message?

What method are you using to import; proc import, data step with infile statement, the Enterprise Guide import wizard or something else?

If the data set gets created you can check the type associated with the variable afterwards, however if you define the variable with a length statement you will ensure it gets created as numeric.

 

Andy

New Contributor
Posts: 3

Re: Reading column as numeric (SAS is guessing character)

Yes, because I know that a certain column is supposed to be numeric, but using proc contents i see that it assigned character. To import I used proc import datafile=datafile dbms=csv replace; getnames=yes;

 

I am not sure what procedure to use so that I can specify numeric type. Im new to SAS,

 

thanks,

Grand Advisor
Posts: 17,393

Re: Reading column as numeric (SAS is guessing character)

You have two options. 

1. Change GUESSINGROWS option to a large number. SAS scans a number of rows to assign types. Increasing the number of rows scanned (and time) will allow a more accurate result. 

2. Manually specify the type and format for all your columns. Unfortunately you can't just specify one. However, after you run your Proc import look at your log. The code is there. You can copy it and customize the fields you need. Hint: hold down ALT while selecting code from the log and you can select the code without the line numbers. 

Grand Advisor
Posts: 10,223

Re: Reading column as numeric (SAS is guessing character)


dleal90 wrote:

Yes, because I know that a certain column is supposed to be numeric, but using proc contents i see that it assigned character. To import I used proc import datafile=datafile dbms=csv replace; getnames=yes;

 

I am not sure what procedure to use so that I can specify numeric type. Im new to SAS,

 

thanks,


I would be more likely to suspect no actual data in the first rows then that the data type is creating missing values. If the column is treated as Character then numbers merely appear as text.

 

Look in your raw data file for sequential commas  like:   somevalue ,, another value.

The variable that should appear between the commas is missing. And proc import usually assgns those as $1 if there is not any value within the guessingrow range.

 

If that is the case however the solution is VERY easy. The proc import generated datastep code that should appear in the log.

Copy that code from the long into the editor (or code node or whatever) and change the informat for that variable from $1. to an appropriate numeric such as best12.

 

New Contributor
Posts: 4

Re: Reading column as numeric (SAS is guessing character)

Since you are reading CSV, besides using PROC IMPORT, you can also using INFILE to read external file such as CSV.

You can explicitly define the INFORMAT for those columns and it will solve the issue.
Ask a Question
Discussion stats
  • 5 replies
  • 365 views
  • 2 likes
  • 5 in conversation