Importing csv files

Accepted Solution Solved
Reply
Trusted Advisor
Posts: 1,204
Accepted Solution

Importing csv files

Hi Forum,

I've 20 csv files and I need to import those into SAS. There are fixed number of variables (around 290) with mixed types across all the files. I am using proc import but not sure I'll get the right data into sas or not. Becasue as far as I know proc import defines variable attributes from the first line. Any suggestions on the one please?

Regards,

Naeem


Accepted Solutions
Solution
‎04-30-2014 05:40 PM
Super User
Posts: 10,552

Re: Importing csv files

Proc import has the option Guessingrows for CSV, so you can specify how many rows to use to guess whether your data is numeric,character or date/time.

Are your files supposed to have the same format? You may get inconsisten results if one data set has a character value when others don't using proc import.

I generally use proc import to generate a starting data step to read the file. After running proc import the log should have the code generated. If running proc import in a program editor window you can press F4 (unless you have modified the Keys ) that will bring the data step code into the top of the editor.

Examine and modify as needed. Usually I change ID type variables to character. You can also see if you have dates that the program is guessing correctly which informat and/or format for date and time variables. Some of your data sources may export a datetime but you only want date so that is a likely change.

If the files have the same layout then you can use the same program just changing the infile and data set name to read each. Though if this is the case I suggest making any name type field long enough and verify that you didn't cut some field short, especially if a rarely populated field is used. It helps if you can get a layout document that describes how wide the fields may be and whether they are expected to be numeric or character.

Hint: If you don't want to do arithmetic, means, sums and such, it isn't numeric.

View solution in original post


All Replies
Solution
‎04-30-2014 05:40 PM
Super User
Posts: 10,552

Re: Importing csv files

Proc import has the option Guessingrows for CSV, so you can specify how many rows to use to guess whether your data is numeric,character or date/time.

Are your files supposed to have the same format? You may get inconsisten results if one data set has a character value when others don't using proc import.

I generally use proc import to generate a starting data step to read the file. After running proc import the log should have the code generated. If running proc import in a program editor window you can press F4 (unless you have modified the Keys ) that will bring the data step code into the top of the editor.

Examine and modify as needed. Usually I change ID type variables to character. You can also see if you have dates that the program is guessing correctly which informat and/or format for date and time variables. Some of your data sources may export a datetime but you only want date so that is a likely change.

If the files have the same layout then you can use the same program just changing the infile and data set name to read each. Though if this is the case I suggest making any name type field long enough and verify that you didn't cut some field short, especially if a rarely populated field is used. It helps if you can get a layout document that describes how wide the fields may be and whether they are expected to be numeric or character.

Hint: If you don't want to do arithmetic, means, sums and such, it isn't numeric.

Trusted Advisor
Posts: 1,204

Re: Importing csv files

Thanks guys for your valuable input.

@ballardw: My files have the same format.

@data_null_: I don't know attributes of the variables. Actually this is clickstream data distributed in 20 files each having same 290 variables.



Super User
Posts: 17,963

Re: Importing csv files

Either use @ballardW solution or consider trying EG.

No matter what, there's going to be some manual work, so spend some time getting one file correct then you can use that for the other 19.

Trusted Advisor
Posts: 1,204

Re: Importing csv files

Thanks @ballardW solution for your advise. I figured it out based on your provided solution. Thanks all other participants for their contribution in this discussion.

Regards,

Naeem

Respected Advisor
Posts: 3,777

Re: Importing csv files

If you know the attributes of the variables it is best to write your own program.  If all 20 files have the same variables then you can read all 20 in one data step.

Super User
Posts: 9,691

Re: Importing csv files

As NULL said , you can import them all once in a data step by option FILEVAR= .

And if you have data structure file know variables name ,length and type that would be best.

Otherwise, you could make a wide width to make sure import correctly .

input (var1-var209) (: $200.) ;

Xia Keshan

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 846 views
  • 2 likes
  • 5 in conversation