Converting Numeric to Character Columns in Macro

[alicezwang96]

I have a macro that I'm using to read in csv files from a folder, which  will later append. The macro is reading some columns from certain files as numeric and others as characters. The macro I'm using is this:

%macro drive(dir,ext); 
   %local cnt filrf rc did memcnt name;

   %let cnt=0;
   %let filrf=mydir;

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

   %if &did ne 0 %then %do;
      %let memcnt=%sysfunc(dnum(&did));
      %do i=1 %to &memcnt;
         %let name=%qscan(%qsysfunc(dread(&did,&i)),-1,.);
         %if %qupcase(%qsysfunc(dread(&did,&i))) ne %qupcase(&name) %then %do;
            %if %superq(ext) = %superq(name) %then %do;
               %let cnt=%eval(&cnt+1);
               %put %qsysfunc(dread(&did,&i));
               proc import datafile="&dir\%qsysfunc(dread(&did,&i))" 
				out=ifb&cnt 
                  dbms=csv replace;
                  guessingrows=max;
				  example = input(example_new, 6);
				  drop example;
               run;
            %end;
         %end;
       %end;
    %end;
  %else %put &dir cannot be opened.;

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

I'd like to specify certain columns as either character or numeric in the macro. In the PROC IMPORT section of this datafile is where I've tried to do this but I end up with the error that the statement is not valid or out of proper order.

[Astounding]

SAS makes these decisions for a reason.  So until you understand what the problem is, designing a solution is silly.

When all the values for a field are legitimate numbers, SAS reads them as numeric.  But when there is a mix, SAS reads them as character because it has to.  What would you want to happen if your "numeric" field contains values like:

Inc.

N/A

5,280

< 5

You need to find what the character values are, and decide what numbers they should become, before you can design a solution.

[ballardw]

If these files are supposed to be of the same structure, i.e. the same columns with the same sorts of values then save yourself a lot of headaches and write a data step using the description of the files to set lengths and proper informats. Reliance on Proc Import for reading such multiple files is going to lead to frustration because the Procedure makes guesses based on the content of every single file and will have problems.

You can use the result of proc import and the generated data step as a guideline to the data step you need. Copy the

code from the LOG, paste into the editor and clean up (remove line numbers for example). Then modify the INFORMAT statements to the proper length and type.

Generally if Proc Import guesses a variable to be character it usually means there is something that appears in the data that is not a simple numeric value. Possibly text like "Null" "NA" "Missing" or values like "<0.0001". These will still likely generate invalid data messages in the log when you specify a numeric informat. What do do depends on your data.

Also, before even attempting such a macro get plain code that works.

Just scanning your code I see in the middle of your Proc Import code

			  example = input(example_new, 6);
				  drop example;

and knew you had errors because such is not valid in Proc Import.

Note that you can use a data step to read multiple text files at one go if the intent is to read them all into a single data set.

ANY time you get errors show the code and error from the long.

[Tom]

If you know how the variables should be defined why are you using PROC IMPORT?

If you just read all of the files at once you won't need the macro code to hunt for the filenames, just use an * wildcard in the file path of the INFILE statement.

Just fill in the details of how to read the variables in the INPUT statement.  If any of the variables REQUIRE that a format be attached (mainly only needed for DATE or DATETIME values) then add a FORMAT statement.

data want ;
  length fname filename $256;
   infile "&dir/*.&ext" dsd filevar=fname truncover ;
   input @;
   if fname ne lag(fname) then delete;
   filename=fname;
   input ..... ;
run;

[pink_poodle]

Import them then convert a list of character variables to numeric using this macro:

%macro to_numeric(varlist);

%local i var;

%do i=1 %to %sysfunc(countw(&varlist));

%let var=%scan(&varlist,&i);

_n_&i = input(&var,??32.);

drop &var;

rename _n_&i = &var;

%end;

%mend to_numeric;

data file1 ;

set file1 ;

%to_numeric(field1 field6);

run;

[Kurt_Bremser]

NEVER (and I mean NEVER) use PROC IMPORT for csv files, and especially not for multiple files that you want to combine later on.

Write the data step yourself (or take the data step from the first import run and adapt it).

[pink_poodle]

Either that or save CSV to xcel then import as usual.

[ballardw]

---

@pink_poodle wrote:
Either that or save CSV to xcel then import as usual.

---

Which can corrupt data, changing values. I have seen thinks like part numbers such as 5-3 become DATES when Excel opens the files. And saving them or importing that result you would now have something like 44325 or what ever the actual number behind May 3, 2022. Or Excel does arithmetic on the result.

[Kurt_Bremser]

---

@pink_poodle wrote:
Either that or save CSV to xcel then import as usual.

---

Which would be the most foolish things you could do, with even more bad consequences than using PROC IMPORT on a csv file. Not only would you still have the guessing of PROC IMPORT, you would add alterations caused by Excel trying to "interpret" the data.

DO NOT EVEN THINK ABOUT IT.