BookmarkSubscribeRSS Feed
dleal90
Calcite | Level 5

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

5 REPLIES 5
andy_self
SAS Employee

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

dleal90
Calcite | Level 5

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,

Reeza
Super User

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. 

ballardw
Super User

@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.

 

alberts
Fluorite | Level 6
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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 1947 views
  • 2 likes
  • 5 in conversation