Desktop productivity for business analysts and programmers

importing csv file by changing multiple different characters.

Reply
Regular Contributor
Posts: 220

importing csv file by changing multiple different characters.

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;                                                                                                                                                                                           

     %end;                                                                                                                                                                                            

   %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)

Attachment
Super User
Super User
Posts: 6,845

Re: importing csv file by changing multiple different characters.

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.

Regular Contributor
Posts: 220

Re: importing csv file by changing multiple different characters.

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

Super User
Super User
Posts: 6,845

Re: importing csv file by changing multiple different characters.

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.

Regular Contributor
Posts: 220

Re: importing csv file by changing multiple different characters.

Thanks Tom...would help...

Regular Contributor
Posts: 220

Re: importing csv file by changing multiple different characters.

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?

Occasional Contributor
Posts: 5

Re: importing csv file by changing multiple different characters.

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

Ask a Question
Discussion stats
  • 6 replies
  • 472 views
  • 1 like
  • 3 in conversation