Variable 'PAT_MRN_ID' has been defined as both character and numeric - help with formatting

[kmckin]

I'm trying to do a merge on the variable 'PAT_MRN_ID' and keep getting the error 'Variable PAT_MRN_ID' has been defined as both character and numeric. How should I modify my code so that the format for this variable is consistent? 

libname hrif 'L:\PRUNIT\PROJECTS\Active Projects\HRIF\Dec2018';
run;

data HRIF; set hrif.t12linkhrif;  

run;

proc sort data = HRIF;  

	by pat_mrn_id;  

run;

data I2E; set hrif.DECNOTESI2E;  

run;

proc sort nodupkey data = I2E;

	by pat_mrn_id;

run;

data hrifnotes;  

	merge HRIF (in = a) I2E (in = b) ;  by pat_mrn_id;

	if a = 1 and b = 1;

	keep pat_mrn_id facility text; 

run;
proc sort nodupkey data = hrifnotes;

	by pat_mrn_id;

run;

ods html;

proc print noobs;

run;

ods html close;

[PeterClemmensen]

Run this

proc contents data=HRIF;
run;

proc contents data=I2E;
run;

and check the pat_mrn_id variable in the two outputs. Then, convert the variable to the proper type.

[ballardw]

99% of the time this issue involves how the data was brought into SAS and often involves Proc Import reading an external file. Proc Import guesses as to variable type, length and format and by default only examines a few rows before guessing.

The optimal method involves going back to read portion and making sure that you read the data as you need it.

You can make a fix by creating a new data set that renames  existing variable and create new variable of the desired name with either a Put function (to create character) or Input function (to create numeric value).

We would need to know specific examples of the variable and whether you want a numeric or character variable, and if character how long it should be. If the length differs from the other set you can have other issues related to truncation or not matching as expected.

Here is a generic example of changing a character value to a numeric:

Data have;
   input mrn $;
datalines;
012345
3445.6
;
run;

data need;
   set have (rename=(mrn=mrn_c));
   /* the informat below needs to be long enough to read the values*/
   mrn = input(mrn_c,f6.);
   drop mrn_c;
run;

And numeric to character

Data have;
   input mrn ;
datalines;
012345
3445.6
;
run;

data need;
   set have (rename=(mrn=mrn_c));
   /* the format below needs to be long enough to read the values*/
   mrn = put(mrn_c,best6. -L);
   drop mrn_c;
run;

The -L in the put function left aligns the value, otherwise there may be leading spaces. Other choices of format may be appropriate depending on actual needs, Z format to have leading zeroes for specified length are common for things like account or product identifiers.

If you have lots of these files the really need to make sure the data is read correctly to begin with.