BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
hjjijkkl
Pyrite | Level 9

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

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.

 

Patrick_0-1620377319837.png

 

 

View solution in original post

6 REPLIES 6
Reeza
Super User
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
Pyrite | Level 9
the two data are already in SAS stored, I didn't import it.
Reeza
Super User
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
Pyrite | Level 9
Can you please give me an example on how to do that? Sorry, I dont have a lot of skills in SAS
Patrick
Opal | Level 21

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.

 

Patrick_0-1620377319837.png

 

 

mkeintz
PROC Star

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.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 6 replies
  • 1927 views
  • 0 likes
  • 4 in conversation