Merging two datasets with a variable in different format (Numeric and Character)

[cphd]

Hi all, 

I am trying to merge two datasets by zipcode, but SAS doesn't process it due to a different format in two files. I tried to modify the format in one of the files but SAS doesn't process it as it's already defined. Can anyone help with this? Thank you. 

 data ret.rrf9819b;
merge ret.rrf9819a other.Ziptocounty;
ERROR: Variable zipcode has been defined as both character and numeric.
  by zipcode;
run;

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set RET.RRF9819B may be incomplete.  When this step was stopped there were 0
         observations and 17 variables.
WARNING: Data set RET.RRF9819B was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
      real time           0.08 seconds
      cpu time            0.00 seconds

 proc datasets library=ret;
modify rrf9819a;
ERROR: You are trying to use the numeric format F with the character variable zipcode in data set
       RET.RRF9819A.
format zipcode 5.0;
quit;

[Reeza]

Show what you've tried so far? This way we can help you figure out if you were on the wrong approach or where you were going wrong with your process.

---

@cphd wrote:

Hi all, 

I am trying to merge two datasets by zipcode, but SAS doesn't process it due to a different format in two files. I tried to modify the format in one of the files but SAS doesn't process it as it's already defined. Can anyone help with this? Thank you. 

 data ret.rrf9819b;
merge ret.rrf9819a other.Ziptocounty;
ERROR: Variable zipcode has been defined as both character and numeric.
  by zipcode;
run;

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set RET.RRF9819B may be incomplete.  When this step was stopped there were 0
         observations and 17 variables.
WARNING: Data set RET.RRF9819B was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
      real time           0.08 seconds
      cpu time            0.00 seconds

 proc datasets library=ret;
modify rrf9819a;
ERROR: You are trying to use the numeric format F with the character variable zipcode in data set
       RET.RRF9819A.
format zipcode 5.0;
quit;

---

[cphd]

So... I have a dataset with zipcode(ZIP) variable; some have 5 digits but some have 9 digits. I subtracted the first 5 digits from the given zipcode(ZIP) variable and created zipcode in ret.rrf9819 file. zipcode is defined as character variable.

I have another file with zipcode and county information and these variables are numeric.  

I want to merge these two files by zipcode. 

[Tom · Accepted solution]

SAS does not care what FORMAT you have attached to a variable when merging.  But it does need the variable to be of the same type.  SAS only has two variable types. Fixed length character strings and floating point numbers.

You should fix the problem up stream in your process so that ZIPCODE is defined consistently from the beginning.  This type of confusion is normally caused by using PROC IMPORT to make a guess about how to define a variable based on the example values it sees in the single file it is trying to import.  So if you can eliminate PROC IMPORT from your process and instead use something where you have control over how the variables are defined.

If you cannot fix it up stream then you will need fix one of the two datasets before you try to merge them.  In general is is best to store ZIPCODE as a character string as the values can have leading zeros and also can have hyphens to store zip+4 values.

But if you did want to convert the values of ZIPCODE in RET.rrf9819a to numbers then you need to make a new dataset.

data fixed;
  set RET.rrf9819a ;
  zipcode_fixed = input(zipcode,5.);
  rename zipcode_fixed=zipcode zipcode=zipcode_char ;
run;
proc sort data=fixed;
  by zipcode;
run;
data ret.rrf9819b;
  merge fixed other.Ziptocounty;
  by zipcode;
run;

[cphd]

Thanks for the solution. It worked perfect!

[cphd]

Hi all, 

I am trying merge two dataset by Zipcode. However, it doesn't allow me to do it because the format of zipcode is different in both files. 

I tried to modify the format, but failed as the format is already defined. Can anyone help me with this? Thank you. 

 data ret.rrf9819b;
merge ret.rrf9819a other.Ziptocounty;
ERROR: Variable zipcode has been defined as both character and numeric.
  by zipcode;
 run;

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set RET.RRF9819B may be incomplete.  When this step was stopped there were 0
         observations and 17 variables.
WARNING: Data set RET.RRF9819B was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
      real time           0.08 seconds
      cpu time            0.00 seconds

data other.ziptocounty;
set other.ziptocounty;
zipcode1=put(zipcode, $5.);
WARNING: Variable zipcode has already been defined as numeric.
run;

[ErikLund_Jensen]

hi @cphd 

You are on the right track, the problem is that you use the format $5. in your second step. Because zipcode is numeric you can't use a character format. Omit the $-sign and use the numeric format 5. instead. It should work.

[PaigeMiller]

The problem is not different formats! The problem is different data types.

If one is numeric and the other is character, you can't do the merge. You have to change variable zipcode to both numeric or both character and then the merge should work.