How to solve numeric and character issue when appending data

[hjjijkkl]

I am appending two dataset and i am getting this error below.

ERROR: Variable age has been defined as both character and numeric.

the problem is one of the dataset has missing value and the other data has a numeric value for the same variable (age). what is the best way to solve this issue? 

below is an example of what the two data look like. 

Table 1

ID	Sex	age
1	F	.
2	F	.

Table2

ID	Sex	age	Education
4	M	33	BA
6	F	67	PHD

Below is what the data should look like after appending (Table1 and Table2)

ID	Sex	age	Education
1	F	.
2	F	.
4	M	33	BA
6	F	67	PHD

[Reeza]

You have to pre-process the data to ensure your types are correct. If you imported the data from CSV or text files you go back and fix it there. If you imported your data from a DB or Excel you have to manually go through a step to confirm them. For some, like in your post it's obvious but for others (like ID) it can be a decision. It's common to store ID as a character so that you never accidentally use it for mathematical calculations for example.

[hjjijkkl]

the two data are already in SAS stored, I didn't import it.

[Reeza]

Then it's similar to Excel/DB you need to go through and standardize your types. First you have to make a decision on the appropriate types and then you have to make them that, then you can merge them. Data cleaning is a pain but necessary.

[hjjijkkl]

Can you please give me an example on how to do that? Sorry, I dont have a lot of skills in SAS

[Patrick · Accepted solution]

To append tables they must have same named columns of the same data type (in SAS either Char or Num).

If you know that the data type should be numeric but in one of your data sets it's character then you could use code as below to convert the character string to numeric and store it in a new variable with the name you need for appending to another table.

data have_ageIsCharDataType;
  infile datalines truncover;
  input age :$10.;
  datalines;
.
12
25
 
.
40
abc
;

data want_ageIsNumDataType;
  set have_ageIsCharDataType(rename=(age=ageIsCharDataType));
  length age 8;
  age=input(ageIsCharDataType,?? best32.);
  drop ageIsCharDataType;
run;

The ?? as part of the input statement will suppress any warnings. Any source string that can't get converted into a numerical value will become missing.

[mkeintz]

If the data set with mistakenly typed character variable is always to be consider a numeric missing value, then you can use the DROP= parameter as below:

data have (where=(sex='M') drop=_:) 
     girls (where=(sex='F') drop=age rename=(_age=age));
  set sashelp.class;
  _age='.';
run;

proc append base=have data=girls (drop=age);run;

But if the variable were not dropped, the proc append would fail with the message you report.