topic Re: Importing multiple large CSV files with varying data formats in one column in SAS Programming

Importing multiple large CSV files with varying data formats in one column

Season — Mon, 16 Jun 2025 07:45:15 GMT

I have a bunch of large CSV files to import, each of which well exceeds the maximum number of rows an Excel spreadsheet can hold. The data are further complicated by a mixture of different formats of the same column. For instance, dates are sometimes recorded as "16/12/20" and sometimes "16 12 20". SAS fails to correctly import the data, seemingly missing those observations with atypical data formats along the course of import. Moreover, as the files are CSV's, handy options of PROC IMPORT like MIXED and SCANTEXT are not available.

My objectives of import are as follows:

(1) Correctly import the data into SAS;

(2) If not possible without the formidable task of finding the atypical observations in the original dataset and changing their formats, then set the columns housing the data with atypical formats as missing values instead of simply skipping them.

Any suggestions? Thank you!

Re: Importing multiple large CSV files with varying data formats in one column

Kurt_Bremser — Mon, 16 Jun 2025 08:01:03 GMT

Read the field into a temporary character variable and add code for detecting the format and doing an appropriate conversion.

If it's only slashes vs. blanks, that can be handled with a single TRANSLATE call.

Re: Importing multiple large CSV files with varying data formats in one column

Kathryn_SAS — Mon, 16 Jun 2025 10:58:44 GMT

If you are using PROC IMPORT or the Import Wizard to import the CSV files, you can use the GUESSINGROWS= statement (Number of rows to guess in the Import Wizard Options box) to tell SAS to look beyond the first 20 rows (the default) to determine a variable's type. If you have different formats in a column, you need it to be imported as character and then you can make changes after the data is read in.

proc import datafile='c:\temp\test.csv'
 out=data_one dbms=csv replace;
guessingrows=1000;
run;

Re: Importing multiple large CSV files with varying data formats in one column

ballardw — Mon, 16 Jun 2025 12:16:07 GMT

@Kathryn_SAS wrote:

...If you have different formats in a column, you need it to be imported as character and then you can make changes after the data is read in.

I've had fairly good luck with SAS using the ANYDTDTE , ANYDTDTM or ANYDTTME with Proc Import generated code when date, datetime or time values appear in different layouts for a single variable. But I don't remember any where space was the delimiter between the data elements.

However that is best when the years are 4 digits. With 2 digit years you have to pray that the order of values entered does match the national language settings.

Re: Importing multiple large CSV files with varying data formats in one column

Season — Mon, 16 Jun 2025 15:28:36 GMT

Thank you! Could you please provide some hint on how to construct temporary variables? Hopefully this does not entail the specification of each and every column in the CSV file like I see in many SAS codes importing CSV's. I have too many columns to specify them one by one.

Re: Importing multiple large CSV files with varying data formats in one column

Season — Mon, 16 Jun 2025 13:33:36 GMT

Thank you so much! That is a very handy option.

By the way, in addition to the GUESSINGROWS statement, are there any useful options and/or statements available in importing CSV's? As I have said in my original question, many of the options and/or statements of PROC IMPORT are not available for CSV's, which is frustrating.

Re: Importing multiple large CSV files with varying data formats in one column

Season — Mon, 16 Jun 2025 13:38:25 GMT

Thanks for the input! Yes, you are right. Space serves as the delimiter in none of the three informats you mentioned in your post.

@ballardw wrote:

However that is best when the years are 4 digits. With 2 digit years you have to pray that the order of values entered does match the national language settings.

I am also rather apprehensive on this issue. With no further information in my dataset, it is likely that I end up designating the digits with the wrong meaning. However, as dates are not the primary concern for the time being, I have to focus on the goal of successful import right now and think about the issue of dates when conditions are permitted.

Re: Importing multiple large CSV files with varying data formats in one column

Tom — Mon, 16 Jun 2025 13:47:23 GMT

What do you mean by varying? Do you mean that it changes from file to file? Or do you mean it changes within a single file?

Your example is not actually a problem for SAS to read (no idea if it is problem for PROC IMPORT to guess how to handle).

data test;
  infile cards dsd truncover;
  input date :ddmmyy.;
  format date yymmdd10.;
cards;
"16/12/20"
"16 12 20"
;

 Obs          date

  1     2020-12-16
  2     2020-12-16

Re: Importing multiple large CSV files with varying data formats in one column

Kathryn_SAS — Mon, 16 Jun 2025 13:39:08 GMT

When importing delimited files, the options and statements that are available are documented here:

PROC IMPORT

Re: Importing multiple large CSV files with varying data formats in one column

Tom — Mon, 16 Jun 2025 13:43:02 GMT

Do you know the structure of the files? If so just write the data step to read it.

Are the files all using the same structure? If so you can write a data step that reads all of the file at once.

If you need a better tool for GUESSING how to read the file try this macro instead of PROC IMPORT.

%CSV2DS()

Re: Importing multiple large CSV files with varying data formats in one column

Season — Mon, 16 Jun 2025 13:43:50 GMT

@Tom wrote:

What do you mean by varying? Do you mean that it changes from file to file? Or do you mean it changes within a single file?

I mean "within a single file".

@Tom wrote:
Your example is not actually a problem for SAS to read (no idea if it is problem for PROC IMPORT to guess how to handle).
data test;
  infile cards dsd truncover;
  input date :ddmmyy10.;
  format date yymmdd10.;
cards;
"16/12/20"
"16 12 20"
;
 Obs          date

  1     2020-12-16
  2     2020-12-16

Yes, I did try PROC IMPORT. Does importing the file with the DATA step necessitates specification of the name and informat of each of every variable in the CSV? That would be a very formidable job as I have possibly thousands of columns in all.

Re: Importing multiple large CSV files with varying data formats in one column

Season — Mon, 16 Jun 2025 13:44:44 GMT

Thanks for your response. But I beg to point out that the options listed in SAS Help are not very informative as many of those listed there are not available for CSV's.

Re: Importing multiple large CSV files with varying data formats in one column

Tom — Mon, 16 Jun 2025 13:49:36 GMT

@Season wrote:
..
Yes, I did try PROC IMPORT. Does importing the file with the DATA step necessitates specification of the name and informat of each of every variable in the CSV? That would be a very formidable job as I have possibly thousands of columns in all.

PROC IMPROT will not handle a delimited text file with thousands of columns. It can only read the first 32K bytes of the header row.

Use the %CSV2DS() macro instead if you need help guessing how to read a delimited file.

https://github.com/sasutils/macros/blob/master/csv2ds.sas

Re: Importing multiple large CSV files with varying data formats in one column

Kathryn_SAS — Mon, 16 Jun 2025 13:50:54 GMT

The documentation link that I sent is specifically for delimited files - csv, tab, dlm - so it does not include statements/options that are not available for you to use for your CSV files.

Another thought would be that you can run PROC IMPORT because it uses DATA step code behind the scenes. Then you could grab that code either from the log or using Run -> Recall Last Submit and then you could make adjustments to that DATA step code as others have suggested. You just wouldn't have to start from scratch.

Re: Importing multiple large CSV files with varying data formats in one column

Season — Mon, 16 Jun 2025 14:36:35 GMT

@Kathryn_SAS wrote:

The documentation link that I sent is specifically for delimited files - csv, tab, dlm - so it does not include statements/options that are not available for you to use for your CSV files.

Thank you for your reminder! I should had taken a second look.

@Kathryn_SAS wrote:
Another thought would be that you can run PROC IMPORT because it uses DATA step code behind the scenes. Then you could grab that code either from the log or using Run -> Recall Last Submit and then you could make adjustments to that DATA step code as others have suggested. You just wouldn't have to start from scratch.

Thank you for your valuable piece of information! So that makes DATA step-based import of CSV files easier. Starting from scratch is so painful and boring for a data analyst who wish to concentrate on data analysis instead of such dull, monotonic work.

Re: Importing multiple large CSV files with varying data formats in one column

Kurt_Bremser — Mon, 16 Jun 2025 15:08:41 GMT

@Season wrote:
Thank you! Could you please provide some hint on how to construct temporary variables? Hopefully this does not entail the specification of each of every column in the CSV file like I see in many SAS codes importing CSV's. I have too many columns to specify them one by one.

First of all: do NOT use PROC IMPORT in production for text files. NEVER. EVER. I mean it. It is much too prone to create inconsistent results, and will sometimes result in a seemingly successful code that created garbage in reality.

You can use PROC IMPORT to create a basic DATA step, which you grab from the log and then modify/expand as needed. You will find that this code is typical for machine-created code. Clumsy, hard to read, and hard to maintain. But it is a beginning.

Here a quick example for converting your strings:

data want;
infile datalines dsd;
input _date :$10.;
format date yymmdd10.;
date = input(translate(strip(_date),"/"," "),ddmmyy10.);
datalines;
16/12/20
16 12 20
;

Note that this is not necessary for the examples you posted. DDMMYY10. will read a date with blanks the same as with slashes.

Re: Importing multiple large CSV files with varying data formats in one column

Season — Mon, 16 Jun 2025 15:27:52 GMT

Thank you so much for your reminder as well as your code! I will try modifying the DATA step codes as recommended.

Re: Importing multiple large CSV files with varying data formats in one column

ballardw — Mon, 16 Jun 2025 22:10:58 GMT

@Season wrote:

Does importing the file with the DATA step necessitates specification of the name and informat of each of every variable in the CSV? That would be a very formidable job as I have possibly thousands of columns in all.

Proc Import will write a basic data step program to read a text file. The code will be in the log and can be copied from the log to the editor, cleaned up and rerun (or issue a RECALL command immediately after the Proc Import to bring the code into the editor.)

The types of informats that often need to be addressed are those where the value contains all digits but you want to maintain leading zeros, such as account numbers. Change the informat to character long enough to hold the value (a $20. or similar). In most cases when modifying a Proc Import generated data step you can drop the FORMAT statements for variables except date, time and datetime variables unless you want to assign custom formats. Also to look out for are columns with mixed use of negative signs and () for negative values, or currency and percent signs that aren't on every value. These may require additional coding as well as in read as character and parse. If you have multiple currency symbols such as dollar, Yen, Pound, Franc and such this might be a very import consideration if you want to manipulate the currency values in any consistent manner.

Check on the assigned informat for your problem variables. If they were read as character but should be dates that is an indication that you may need to create new variables by parsing the values. Check on your national language settings (NLS) to see what order dates are read. OR if you see lots of invalid data messages involving those variables it is one indicator that the order may be different than your NLS and override to read as character and parse.

If you have variables that would best be considered Boolean, i.e. Yes/No, True/False, and such it may be worth creating and using a custom informat so that the results are numeric 1/0 as that will be much easier to work with in most cases going forward instead of a hodgepodge of Y/N T/F character values.

Another consideration not mentioned yet, is if these files are supposed to be of the same layout you should be able to use the same data step to read all of them by changing name of input file and output data set. But it is very likely that lengths of character variables will differ between files. So modify any of the $w. informats to allow for this. I generally start at 15% or so longer than the generated data step. And then check after reading that the values look right. If not make the informat wider and re-read.

A last issue relates to variable names generated from column headings that are either very long (will get truncated at 32 characters) or identical in the source file. If column headings are identical for the first 32 characters of a longer heading the first will get part of the text as the variable name. The others will get VARxxxx where xxxx may be the column number in the file. Identical shorter heading may get numeric suffixes added. Example a file with multiple headings of "Total", the first will have a variable name of Total, the next Total2 (or Total1 been awhile) with incremented numbers for each following.

Recommend setting option VALIDVARNAME=V7 before Proc Import. Dealing with variable names with spaces and non-standard characters gets old real quick having to use the name literal such as 'Stupid variable name'n every where. The V7 option will replace all the special characters with _ and be easier to type (or rename as desired).

One tool to help deal with some of this if you don't have good documentation is to copy the header row of the CSV, assuming it has column headers, and Paste that TRANSPOSED into a spreadsheet. That will give you one "row" per variable to do such things as examine how long the variable names might be, whether different files have different headers (paste into a different column in the spreadsheet and run a comparison of values). If you have a source that has narrative column headings you can with a little work in the spreadsheet get it to create LABEL assignment statements for variables by pasting the variable names from the proc import generated data set into another column (either using the INPUT statement from the code or Proc Contents output) and use spreadsheet functions to create text like varname ="original column heading text goes here".

Any data source that may have "thousands of columns" and doesn't provide documentation as to content of the file, such as expected lengths of character variables and layouts of date, time or datetime values needs to be considered with great suspicion. Without documentation how do you know what anything represents?

Re: Importing multiple large CSV files with varying data formats in one column

Season — Tue, 17 Jun 2025 08:29:52 GMT

Thank you so much for your very detailed summarization of your experiences in dealing with real-world datasets! I think your response deserves much more than a mere like, but unforunately, that is all I can offer. Still, now that you have invested plenty of your time on composing these words, I strongly suggest that you go a step further and transform them into a paper that may eventually appear in SAS User Group or academic journals like Journal of Statistical Software. Your experience is invaluable for both SAS users and beyond.

@ballardw wrote:

@Season wrote:

Does importing the file with the DATA step necessitates specification of the name and informat of each of every variable in the CSV? That would be a very formidable job as I have possibly thousands of columns in all.

Check on the assigned informat for your problem variables. If they were read as character but should be dates that is an indication that you may need to create new variables by parsing the values. Check on your national language settings (NLS) to see what order dates are read. OR if you see lots of invalid data messages involving those variables it is one indicator that the order may be different than your NLS and override to read as character and parse.

Still, I would like to further consult on NLS. I searched on the web and saw Microsoft having a webpage on NLS, but with a slightly different meaning- national language service. I am not sure if the two NLS's are the same, but anyway, could you please brief introduce what national language settings is and what impact does it have on importing datasets into statistical softwares like SAS? I used to think that the mere difference in the language of the interface and log of SAS does not really have an impact on its core capabilities such as loading and editing datasets.

@ballardw wrote:

Any data source that may have "thousands of columns" and doesn't provide documentation as to content of the file, such as expected lengths of character variables and layouts of date, time or datetime values needs to be considered with great suspicion. Without documentation how do you know what anything represents?

Finally, I would like to make a clarification. Your reminder is of great value and I thank you for it. However, the datasets I use does have a documentation. In fact, it is huge as it has a description for every variable therein. Still, only descriptions of variables instead of their intriacacies are documented. In other words, for a given column, I only know that it stands for a date with a particular meaning but do not know that it can take multiple formats like "12/16/15" and "12 16 15". Only when I imported it into SAS did I realize this issue.

Re: Importing multiple large CSV files with varying data formats in one column

Season — Sun, 20 Jul 2025 03:42:46 GMT

I have an additional question on this issue. Since my question is the same for everyone in the chat, I will only post it in a response to @Tom.

My question is: is it possible to import the CSV files in a parallel instead of a sequential manner? My experience in importing such large files is that the importation process of even a single file takes a lot of time. Therefore, "parallel importation", a word I coined from "parallel computing", is preferred. I wonder if SAS can realize this.