topic Re: ERROR: Variable HERD has been defined as both character and numeric. BY USING MERGE in SAS Programming

ERROR: Variable HERD has been defined as both character and numeric. BY USING MERGE

Barkamih — Thu, 04 Apr 2019 17:05:50 GMT


Hi all,

I got error by using both of theses codes in order to merge two datasets 

Data data2004;
merge d2004 county; by herd; run; quit; 

data Data2004;
merge D2004(IN=K) county(IN=Q); 
if K and Q; by herd; run; quit;


and this the log:

355 data Data2004;
356 merge D2004(IN=K) county(IN=Q);
ERROR: Variable HERD has been defined as both character and numeric.
357 if K and Q; by herd; run;

regards

Ibrahim

Re: ERROR: Variable HERD has been defined as both character and numeric. BY USING MERGE

PaigeMiller — Thu, 04 Apr 2019 17:07:15 GMT

I think the error message is relatively clear. You have to fix the problem so in both data sets HERD is numeric, or in both data sets HERD is character.

Re: ERROR: Variable HERD has been defined as both character and numeric. BY USING MERGE

Reeza — Thu, 04 Apr 2019 17:08:16 GMT

How did you create these data sets initially? Did you verify the variable types were imported correctly and what you need?
If not, you need to go back and do that step.

@Barkamih wrote:
Hi all,

I got error by using both of theses codes in order to merge two datasets 

Data data2004;
merge d2004 county; by herd; run; quit; 

data Data2004;
merge D2004(IN=K) county(IN=Q); 
if K and Q; by herd; run; quit;


and this the log:
355 data Data2004;
356 merge D2004(IN=K) county(IN=Q);
ERROR: Variable HERD has been defined as both character and numeric.
357 if K and Q; by herd; run;
regards

Ibrahim

Re: ERROR: Variable HERD has been defined as both character and numeric. BY USING MERGE

Barkamih — Thu, 04 Apr 2019 20:02:51 GMT

how to make it correct?

data county;
herd = '             ';
new_herd = input(herd,               );
drop herd;
rename new_herd = herd;
run;

Re: ERROR: Variable HERD has been defined as both character and numeric. BY USING MERGE

Kurt_Bremser — Thu, 04 Apr 2019 19:56:35 GMT

Provide example data, so we can guide you to the correct informat.

Re: ERROR: Variable HERD has been defined as both character and numeric. BY USING MERGE

Barkamih — Fri, 05 Apr 2019 09:59:55 GMT

i did this code but to convert the variable, but how to complete it correctly?
data county;
herd = ' ';
new_herd = input(herd, );
drop herd;
rename new_herd = herd;
run;

Re: ERROR: Variable HERD has been defined as both character and numeric. BY USING MERGE

Reeza — Thu, 04 Apr 2019 20:10:34 GMT

How did you create these data sets initially? Did you verify the variable types were imported correctly and what you need?

If you imported these files via CSV or a text data set its better to go back to that step and fix it there. If not, youc an convert to a new variable but you need to rename it before you bring it in.

data county;
set oldData (rename = Herd=OldHerd);
*convert to numeric;
Herd =input(herd, 8.);
drop oldHerd;
run;

Re: ERROR: Variable HERD has been defined as both character and numeric. BY USING MERGE

Kurt_Bremser — Fri, 05 Apr 2019 06:28:26 GMT

Which informat you have to use in the input() function depends on the contents of herd. That's why we need examples.

In many cases, the BESTw. informat can work.

Re: ERROR: Variable HERD has been defined as both character and numeric. BY USING MERGE

Shivam — Fri, 05 Apr 2019 05:20:12 GMT

you need to fix the data type in your contributing tables, there is no issue with your merge code

Re: ERROR: Variable HERD has been defined as both character and numeric. BY USING MERGE

Barkamih — Fri, 05 Apr 2019 11:08:10 GMT

Hi, I'm really sorry about that, I was so busy yesterday,

this is an example of the herd variable

so, I have big data that has these numbers and I want to merge some information to these file by a herd.

Herd number means Farm name or farm id,

I got these data in excel file form a company.

data was imported to SAS as a CSV file type.

I'm really confused about how to merge these file together.

best regards to everyone

Ibrahim

Re: ERROR: Variable HERD has been defined as both character and numeric. BY USING MERGE

PaigeMiller — Fri, 05 Apr 2019 12:51:53 GMT

It doesn't help if you type in the data from ONE of the data sets. Typing in the data doesn't help anyway. You have to show us the code that shows how BOTH (not one) data sets were created. Or show us PROC CONTENTS output of BOTH data sets.

Re: ERROR: Variable HERD has been defined as both character and numeric. BY USING MERGE

Tom — Fri, 05 Apr 2019 13:07:01 GMT

@Barkamih wrote:

data was imported to SAS as a CSV file type.

Since you are reading from a text file you can have complete control over how the variables are created.

You probably used PROC IMPORT to read the files. That will just generate a data step to read the file. Copy the data steps and modify the definition of HERD to be the same for both. Then re-run the modified data steps and now your HERD variable will be compatible.

Re: ERROR: Variable HERD has been defined as both character and numeric. BY USING MERGE

Barkamih — Fri, 05 Apr 2019 13:51:45 GMT

this is data county

Alphabetic List of Variables and Attributes# Variable Type Len Format Informat 1


herd	Num	8	BEST12.	BEST32.

Sort InformationSortedbyValidatedCharacter Set

herd

YES

ANSI

this is data d 2004

Alphabetic List of Variables and Attributes# Variable Type Len Format Informat

HERD

Char

$42.

Re: ERROR: Variable HERD has been defined as both character and numeric. BY USING MERGE

Kurt_Bremser — Fri, 05 Apr 2019 13:55:10 GMT

Given your data, a big enough w. informat (eg 12.) will do it. But if you start from a csv file, the optimal way is reading the column as numeric in the first place.