SAS importing two excel files to SAS. One column/variable is text and the other column is numeric

[kmin87]

Hello,

In SAS I am importing two separate excel files and storing them in the work library. The excel files have a "primary" column that has blank numbers and numbers that are in decimals or whole numbers. For example, there are 0.40 , 1, 0.333, or a blank.

Eventually I will create a new data set based off these two sets. The primary column is text from one file after I import it and the primary column is a numeric in the other file when I import. 

I get an error that one variable is numeric and the other variable is character.

How do I normalize the data? I want to make sure both the primary columns are either numeric or text before I do a new data with combining or set the two data sets.

The column is primary and it is a text type when I import it in the sas 

proc import out=prf1
 datafile= "filepath/example.xlsx"
dbms=excelcs
sheet="sheetname";
run;

The column is primary and it is a numeric type when I import it in the sas 
proc import out=prf2
 datafile= "filepath/example2.xlsx"
dbms=excelcs
sheet="sheetname";
run;

So I tried this

data prf2;
   orig = 'primary ';
   new = put(orig, $8.);
   drop primary ;
   rename new=primary ;
run;

I opened the fsview data and the primary column has converted to numbers but it rounded all to single digits, for example .643 became 1

data prf1;
   orig = 'primary ';
   new = input(orig, 8.);
   drop orig;
   rename new=primary ;
run;

When I tried this way, the primary column is blank and it doesnt have the values from the original primary column. It was all periods.

[ballardw]

Welcome to the wonderful world of bad data formats imposed on the world by Micro$oft and Excel.

Excel does not impose any characteristics on columns so similar Excel files can contain text and numbers in the same column. Proc Import by default only examines a few rows of data to set properties like type and length. Depending on your data that may mean one file has all "numeric" appearing values in the first few but the other file has at least one value with other than digit characters.

You might get lucky if the MIXED=YES option works for proc import in your environment that may make a column with mixed types of values as character. Might.

For another recent with some suggestions that I'm too lazy to retype

https://communities.sas.com/t5/New-SAS-User/Error-Variable-has-been-defined-as-both-character-and-nu...

BTW, this possibly the single most common "problem" in questions to this forum for the past 3 or 4 years (if not longer).

[Tom · Accepted solution]

Your data steps are making completely new datasets as they are not reading in any data.  You need a SET statement to tell it what data to read into the data step.

Also you current code is very strange.  You are setting ORIG to the string 'primary'.  The setting NEW to the same string by passing the string from ORIG to the PUT() function and using the basic $ format to re-write the string as the same string.

data prf2;
   orig = 'primary ';
   new = put(orig, $8.);
   drop primary ;
   rename new=primary ;
run;

If you want to make a version of PRF2 that converts PRIMARY from NUMERIC to CHARACTER you would do something like this:

data prf2_fixed;
  set prf2;
  new=put(primary,F8.-L);
  drop primary;
  rename new=primary;
run;

If you want to make a version of PRF1 that converts PRIMARY from CHARACTER to NUMERIC you would do something like this:

data prf1_fixed;
  set prf1;
  new=input(left(primary),32.);
  drop primary;
  rename new=primary;
run;

[Tom]

Try using the XLSX database engine instead of EXCELCS and check if it does a better job of converting your particular XLSX files into datasets.