Novice needs help- Error message using MERGE statement - variable with character & numeric values

[hlg23]

Hello, I took an "into to SAS" course and now I'm trying to apply what little I know at my internship to impress a potential future employer! I'm using SAS 9.4 

I did an observational study where multiple researchers (student volunteers) gathered data and entered it into Excel files. I created separate SAS datasets from the excel files and now I am trying to merge the sas datasets by a common index variable "siteID". When I ran the code, there was an error in my log that two of the variables contained both numeric and character variables. The variables are "obstart" and "obend" and they are time variables (ie time that the observation started and time the observation ended), so for example 12:15pm. 

How do I let SAS know that my variables "obstart" and "obend" contain numeric and character variables (time)? Do I need to do a PROC FORMAT procedure before I do the MERGE statement? 

Here's my code....sorry I didn't take a screenshot of the log! 

libname hil 'E:\sasfiles'; 
run;

/*Import Bianca Obs DATA tab*/
proc import out=bcdata
	datafile= 'E:\sasfiles/bcobs'
	DBMS= XLSX replace;
	sheet= "data";
	getnames=yes;
run;
proc print data=bcdata;
run;

/*Import SIPS tab for Bianca*/
proc import out=sipsbc
	datafile= 'E:\sasfiles/bcobs'
	DBMS= XLSX replace;
	sheet="sips";
run;
/*Import OTHER tab for Bianca*/
proc import out=bcother
	datafile= 'E:\sasfiles/bcobs'
	DBMS= XLSX replace;
	sheet="other";
run;

/*Import Hedy Obs DATA tab*/
proc import out=hldata
	datafile= 'E:\sasfiles/hlobs'
	DBMS= XLSX replace;
	sheet= "data";
	getnames=yes;
run;
proc print data=hldata;
run;

/*Import SIPS tab for Hedy*/
proc import out=sipshl
	datafile= 'E:\sasfiles/hlobs'
	DBMS= XLSX replace;
	sheet="sips";
run;
/*Import OTHER tab for Hedy*/
proc import out=hlother
	datafile= 'E:\sasfiles/hlobs'
	DBMS= XLSX replace;
	sheet="other";
run;
/*Sort data to merge*/
proc sort data=bcdata; BY siteID; 
run;
proc sort data=bcother; BY siteID; 
run;
proc sort data=sipsbc; BY siteID; 
run;
proc sort data=hldata; BY siteID; 
run;
proc sort data=hlother; BY siteID; 
run;
proc sort data=sipshl; BY siteID; 
run;


/*Merge Hedy and Bianca data sets*/
data hil.hbobs;
merge bcdata bcother sipsbc hldata hlother sipshl; 
by siteID; 
run; 
proc print data=hil.hbobs; run; 

[Jagadishkatam]

I guess, the issue over here is there are same variables in multiple datasets that you are merging and in those datasets in some the same variable type is character and in some it is numeric. so before merging these datasets you need to ensure that the same variables in all these datasets should be same type. You need to either make then numeric or character.

[andreas_lds]

@Jagadishkatam already mentioned the reason for the error. This error is quite common when using Excel as data-source and the guessing procedure proc import.

You have two options:

1. move the data to csv and import those files with a data-step.
2. Check which columns have same name but different types and length, add data-steps fixing wrong type and length.