topic Re: Importing a csv file in ODS and Base Reporting

Importing a csv file

deleted_user — Wed, 05 Sep 2007 16:36:38 GMT

Is there a way that I can have SAS check each variable to determine if it should be character, or numeric and the appropraite length? I currently use the import wizard then copy from the log and modify it as needed, but with many files to read in, this get very time consuming.

Thanks

Re: Importing a csv file

Cynthia_sas — Wed, 05 Sep 2007 16:54:58 GMT

Hi,
There is a GUESSINGROWS option, but I can't remember whether it is for the LIBNAME engine or PROC IMPORT. You could check with the doc or with Tech Support.

cynthia

Re: Importing a csv file

deleted_user — Wed, 12 Sep 2007 08:54:28 GMT

If you are using a wizard in Enterprise Guide or something else, it will be a tedious chore for you. I usually read CSV files with custom-written SAS code that validates all values.

I read in all numeric and date values as character with names such as START_DATE_CHAR, ACCOUNT_NUMBER_CHAR, and so on. Then I check each one like this:

infile
"account_info.csv" dsd firstobs=1 lrecl=400 missover print;

input
account_number_char $10.
start_date_char $15.;

if compress(start_date_char) ne '' then
start_date = input(start_date_char, ddmmyy10.);
else
start_date = .;

if compress(account_number_char) not in ('-', '?', '') then
account_number = input(account_number_char, 8.);
else
account_number = 0;

Then DROP the _CHAR variables. In this manner, you can trap any data exception from Excel and handle it exactly the way you want. You can also check field lengths. But you'll need to write SAS code. Message was edited by: Roddy