03-06-2013 10:45 AM
Hi friends, I am trying to import couple of CSV files from a folder and I am using attached code but import process is getting failed because CSV file has multiple different characters like (**, ***, ****, -, _, (X), N). Is there any way to twist this code so I can get it done? I want to replace all different character with dot (.).
Can you please help?
/*This is a code i am using*/
/* Assigns the fileref of mydir to the directory and opens the directory */
/* Returns the number of members in the directory */
/* Loops through entire directory */
%do i = 1 %to &memcnt;
/* Returns the extension from each file */
/* Checks to see if file contains an extension */
%if %qupcase(%qsysfunc(dread(&did,&i))) ne %qupcase(&name) %then %do;
/* initialize variable */
/* Checks to see if the extension matches the parameter value */
/* If condition is true prints the full name to the log */
%if (%superq(ext) ne and %qupcase(&name) = %qupcase(&ext)) or
(%superq(ext) = and %superq(name) ne) %then %do;
%let file= %qsysfunc(dread(&did,&i));
%let dslist=&dslist %scan(&file,1,.);
Proc import datafile="&dir.%unquote(&file)" out=%scan(%unquote(&file),1,.)
/* Closes the directory */
/* First parameter is the directory of where your files are stored.
This value must end with a '\' ! */
/* Second parameter is the extension you are looking for. */
03-06-2013 11:43 AM
If the files are all in the same format then just create a program to read them.
Depending on PROC IMPORT to figure out how to read your CSV files can lead to this type of issue. Better that you write the INPUT statement yourself. You can recall the code that PROC IMPORT generates and clean it up.
03-06-2013 01:13 PM
I didn't see any special characters. I saw fields that ** in them that SAS had guessed where numeric.
You could read the column as character instead of number and then ** would not be a problem.
Or you could read it as numeric, but use a user defined INFORMAT that maps '**' to missing or special missing.
03-07-2013 01:19 PM
Hi Tom, i am still having trouble to importing data. I am pretty sure that ** or *** or ***** having specific formula in a sheet and if i m trying to import it , its come up with same error - " Variable HC04_VC41 has been defined as both character and numeric". there are lot of different variable like this in a sheet.
i did try to paste special this CSV esheet with value only but its not chaning too...
05-27-2013 09:14 PM
How about adding the guessingrows=32767; in your Proc import, seems likely the format and informat was defined for the same variable.