CODEWITHSUNDEEP
typessas
Rookatu
Rookatu

Reputation: 1507

SAS variable has no type

I have a database I import through a PROC SQL step in a program in SAS EG 5.1

Originally the code I used to do this was as follows:

CREATE TABLE WORK.TABLE AS 
     SELECT * FROM connection to some_source (
     SELECT *
     FROM some_table
     WHERE LOGIN_ID IN (SELECT ID FROM WORK.ID_List);;

This generated the error: "Expression using IN has components that are of different data types."

These should both be numeric (I know that ID is numeric) but I assumed that LOGIN_ID must be represented as character and resolved to convert it to numeric in this step. I fumbled around google for quite a while trying to find a way to do this until a colleague told me it cannot be done in this step unless I list out all 40 columns and use AS, which is going to be a last resort. I'm not sure if he's correct.

So I deleted the WHERE clause to get a look at the data. It all looks fine, and LOGIN_ID certainly looks numeric, so I tried using VTYPE to check its type. What I got is a column of null values (dots). Again, google was no help here.

So, how can these numbers not have a type? Also, any ideas about how to solve the original problem are greatly appreciated!

Upvotes: 1

Views: 195

Answers (1)

Alex A.
Alex A.

Reputation: 5586

You can try this:

create table work.table as
select a.*
from connection to some_source (
    select *
    from some_table
) as a
inner join work.id_list as b
on cats(a.login_id) = cats(b.id);

The SQL query passed to the connection has to be valid in the host database system, so it's a good idea to avoid referencing SAS tables from within that query. To get the result you're hoping for with the in operator, you can inner join on your SAS dataset.

The cats() function blindly casts its argument to a character string and removes and leading and trailing blanks. So if login_id and id are not of the same type, this will ensure that they can still be matched appropriately. Note that missing numeric values will become "." and missing character values will become "", so it's a good idea to know whether you have missing values in either column on which you're joining.

You only need to list all of the columns rather than using select * if you want to explicitly change the type in the connection query. The method above will avoid having to do that.

Upvotes: 2

Related Questions