topic importing csv file by changing multiple different characters. in SAS Enterprise Guide

importing csv file by changing multiple different characters.

sas_9 — Wed, 06 Mar 2013 15:45:19 GMT

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?

Thanks.

/*This is a code i am using*/

%macro drive(dir,ext);

%let filrf=mydir;

/* Assigns the fileref of mydir to the directory and opens the directory */

%let rc=%sysfunc(filename(filrf,&dir));

%let did=%sysfunc(dopen(&filrf));

/* Returns the number of members in the directory */

%let memcnt=%sysfunc(dnum(&did));

/* Loops through entire directory */

%do i = 1 %to &memcnt;

/* Returns the extension from each file */

%let name=%qscan(%qsysfunc(dread(&did,&i)),-1,.);

/* Checks to see if file contains an extension */

%if %qupcase(%qsysfunc(dread(&did,&i))) ne %qupcase(&name) %then %do;

/* initialize variable */

%let dslist=;

/* 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,.)

dbms=csv;

getnames=yes;

run;

%end;

/* Closes the directory */

%let rc=%sysfunc(dclose(&did));

%mend drive;

/* 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. */

%drive(\\xyz\census economic\,csv)

Re: importing csv file by changing multiple different characters.

Tom — Wed, 06 Mar 2013 16:43:12 GMT

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.

Re: importing csv file by changing multiple different characters.

sas_9 — Wed, 06 Mar 2013 18:02:02 GMT

actually i am looking for code which could cover/take care of all these special character...

Re: importing csv file by changing multiple different characters.

Tom — Wed, 06 Mar 2013 18:13:25 GMT

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.

Re: importing csv file by changing multiple different characters.

sas_9 — Thu, 07 Mar 2013 13:57:19 GMT

Thanks Tom...would help...

Re: importing csv file by changing multiple different characters.

sas_9 — Thu, 07 Mar 2013 18:19:37 GMT

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

any clue?

Re: importing csv file by changing multiple different characters.

firefly — Tue, 28 May 2013 01:14:29 GMT

How about adding the guessingrows=32767; in your Proc import, seems likely the format and informat was defined for the same variable.