topic Re: Data Type Error in Proc sql in SAS Programming

Data Type Error in Proc sql

mayasak — Sun, 11 Jun 2023 20:37:24 GMT

I Have the following data sets: AllData, Death, and Live. AllData has more observations than Death and Live combined.

I want to have a data set of AllData excluding Death observations. I keep getting these errors when I'm using the code:

proc sql;
create table Live as
select * from AllData
except
select * from Death
;
quit;

WARNING: A table has been extended with null columns to perform the EXCEPT set operation.
ERROR: Column 5 from the first contributor of EXCEPT is not the same type as its counterpart from the second.
ERROR: Numeric expression requires a numeric format.
ERROR: Column 6 from the first contributor of EXCEPT is not the same type as its counterpart from the second.

The variable causing the errors is the DOB having the Types as follows:

I'm not sure why DOB in both datasets (imported from .xlsx with a 'date' type) are imported into SAS as Num and why are they causing this issue.

**I also used this code but it's giving AllData dataset without any death exclusions!

data live2;
set AllData;
if _n_ = 1
then do;
  declare hash d (dataset:"Death");
  d.definekey("Patient_Name");
  d.definedone();
end;
if d.check() ne 0;
run;

Your help and support is greatly appreciated.

Thank you

Re: Data Type Error in Proc sql

PaigeMiller — Sun, 11 Jun 2023 20:56:23 GMT

ERROR: Column 5 from the first contributor of EXCEPT is not the same type as its counterpart from the second.
ERROR: Column 6 from the first contributor of EXCEPT is not the same type as its counterpart from the second.

The mismatch in data type is in Column 5 and Column 6 in ALLDATA. Please examine those variables carefully in both data sets.

Re: Data Type Error in Proc sql

mayasak — Sun, 11 Jun 2023 21:06:31 GMT

I had this in my proc contents for both datasets. I can't find any discrepancies. As I mentioned before, DOB seems to cause the issue but I'm not sure how!

Re: Data Type Error in Proc sql

PaigeMiller — Sun, 11 Jun 2023 21:21:02 GMT

Please don't be so stingy with your screen captures, show us more of the output from PROC CONTENTS so we can see the relevant parts including which variable is 5 in ALLDATA and which variable is 6 in ALLDATA, and including the part of the PROC CONTENTS output that indicates which data set is which..

As I mentioned before, DOB seems to cause the issue but I'm not sure how!

No! It's column 5 and column 6, one of which may be DOB, but there's another variable we need information on as well.

Re: Data Type Error in Proc sql

mayasak — Sun, 11 Jun 2023 21:18:02 GMT

Thank you PaigeMiller.

Here's the proc contents of AllData

And the proc contents of Death:

Please let me know if I'm missing any other information.

Re: Data Type Error in Proc sql

Tom — Sun, 11 Jun 2023 21:59:00 GMT

Just be explicit about which variables you want to select. (Don't use the * shorthand).

But what is your criteria for saying there is a match?

Do you only need to have them match on SSN?

Or perhaps on NAME and DOB?

Re: Data Type Error in Proc sql

mayasak — Sun, 11 Jun 2023 22:38:41 GMT

Hi Tom,

Thank you for the response.

For the second code, I tried it because I had the errors in the first code, I was trying to match on Patient_Name only.

Re: Data Type Error in Proc sql

Tom — Sun, 11 Jun 2023 22:46:45 GMT

That is simple to do:

proc sql;
create table Live as
  select * from AllData
  where Patient_Name not in (select Patient_Name from Death)
;
quit;

But depending on how clear (or really how consistent) your data is might not do exactly what you want.

At a minimum you might want to ignore the case of the letters in the Patient_Name variable.

where upcase(Patient_Name) not in (select upcase(Patient_Name) from Death)

Re: Data Type Error in Proc sql

mayasak — Sun, 11 Jun 2023 23:47:51 GMT

Thank you Tom