2

I am trying to subset my data with PROC SQL, and it is giving me an error when I use my variable TNM_CLIN_STAGE_GROUP. Example below:

PROC SQL; 
create table subset as
select ncdb.*
from ncdb
where YEAR_OF_DIAGNOSIS>2002  
AND SEX = 2
AND LATERALITY IN (1,2,3)
AND HISTOLOGY = 8500
AND TNM_CLIN_STAGE_GROUP = 1;
quit;

ERROR: Expression using equals (=) has components that are of different data types.

When I run the same code, but take out the variable TNM_CLIN_STAGE_GROUP, the code works. Anyone know what the problem with that variable's name is?

Joe
  • 62,789
  • 6
  • 49
  • 67
vokey588
  • 203
  • 1
  • 3
  • 9

2 Answers2

3

That error indicates a difference in type. SAS has only two types, numeric and character, so the variable is probably character; verify the specific values, but in general it likely needs quotations (single or double, doesn't matter in this case).

If it is not a hardcoded value, but a value of another variable, you can use PUT to convert to character or INPUT to convert to numeric, whichever is easier to convert based on the data.

SAS in a data step will happily convert this for you, but in SQL and SQL-like (WHERE statements) it does not automatically convert character to numeric and vice versa; you must provide the correct type.

Joe
  • 62,789
  • 6
  • 49
  • 67
0

Before doing equality, check what you are trying to compare.

Check the structure of you ncbd table, in particulary field type of TNM_CLIN_STAGE_GROUP

You would see the real type, if its a varchar, you need to use single quote like @JChao suggest in is comment.

If its another type, so you need to adapt the comparator or use cast if you don t have choice.

Mançaux Pierre-Alexandre
  • 858
  • 1
  • 11
  • 42