BookmarkSubscribeRSS Feed
sas_9
Obsidian | Level 7

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)

6 REPLIES 6
Tom
Super User Tom
Super User

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.

sas_9
Obsidian | Level 7

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

Tom
Super User Tom
Super User

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.

sas_9
Obsidian | Level 7

Thanks Tom...would help...

sas_9
Obsidian | Level 7

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?

firefly
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 1350 views
  • 1 like
  • 3 in conversation