how do i Converting Character columns to numeric

[dougj03]

Hello, I am using a PROC IMPORT OUT to read in an ecxel spreadsheet with 6 different tabs i am needing to reference. 
The first 2 tabs that i am referencing has zero values in the rows on the excel doc that is causing my columns to read as character vs numeric and the other 4 tabs have values in the same columns. In my data file_1 step is where i am receiving the error saying my variables are both Character & numeric and i need to columns to be numeric.

PROC IMPORT OUT= WORK.file_&filename.
DATAFILE= "\File path"
DBMS=xlsx REPLACE;
sheet="&sheet";
GETNAMES=yes;
RUN;

/*proc append data=file_&filename. base=read force; run;*/
%mend;

%read(File,1);
%read(File,2);
%read(File,3);
%read(File,4);
%read(File,5);
%read(File,6);


data xx.File_dummy_;
retain Rep_assigned Date_Assigned Date_Completed Fee_Type  Fee_Amt Coverage_added_and_billed_;
Format Date_Assigned Date_Completed mmddyy10.;
Format Fee_Type BEST.  Fee_Amt dollar7.2;
if _N_ >= 0 then delete;
run;

data file_1;
set xx.File_dummy_  file_1;
run;

[SASKiwi]

The easiest option is to add a dummy line of data to the top of the two offending tabs, then reimport, then delete the dummy lines in SAS. This should mean you don't need to do any conversion.

[Tom]

Best option is not to use EXCEL to store data.  If the data was in a text file or an actual database then you would have control over how the variables were defined.

But that is probably impossible to implement in your case.

If your only concern is for variables that have become character because all of the cells in that column are empty then perhaps the easiest thing to do is DROP the variable(s).  

One way to find out if the variable is empty that should not require too much coding is using the NLEVELS option of PROC FREQ.

So perhpas use something like this:

proc import ... out=THIS_SHEET ...
....
run;
ods output nlevels=nlevels;
proc freq nlevels data=THIS_SHEET ;
  tables _all_ / noprint;
run;

%let dropvars=;
proc sql noprint;
select nliteral(tablevar) into :dropvars separated by ' '
  from nlevels
  where nnonmisslevels=0
;
quit;

proc append base=base_file data=THIS_SHEET(drop=&dropvars) force;
run;