SASPy - Reference existing table for filtering on proc sql statement

[flachboard84]

Hello,

I'm new to SASPy. I previously used R extensively but am currently migrating for performance reasons.

I'm first creating a table that provides a list of members.

Next, I want to create a proc sql data request using that list of members. I tried creating a string that looks like this: ('55555', '64646', '97979', ...) and feeding that into my proc sql statement. However, there seems to be a character limit. I'd prefer not to chunk the list up, do a for loop, and then union the data.

How can I add this list to my proc sql statement to make it work?

Example of method that doesn't work because my string is too long (but works when I have a short string of members):

%%SAS

libname csf "path";

PROC SQL;
    CONNECT TO TERADATA(&TD);
    CREATE TABLE csf.data_table as
    Select * FROM CONNECTION TO TERADATA
    (

    select
        a.member,
        a.clm_nbr,
        a.aud_nbr,
        
    from 
        db.a a

    where
	a.member in &mbr_str.
    );
    DISCONNECT FROM TERADATA;
QUIT;

[sastpw]

Hey, just off the top of my head; I haven't tried it.Could you use a subquery instead of a list.

create a table with one column and all of those values as the rows, then have your where clause criteria be the subquery instead of the list:

```

where a.member in (select mem from temp_table)

``` 

As I don't see any real saspy code, this is just SAS proc sql, I'm guessing the list of members came from python?

You can have that as a dataframe and then use df2sd() to create the table (even in teradata) to then use it in the sql.

If I see more code (saspy, python side), I could probably code up something more specific.

Tom

[flachboard84]

Thanks, Tom! That’s definitely an option. I’m just looking to see if what I’m trying to do is an option as well.

[sastpw]

Ok, well, Teradata (ans SAS) support the in() operator, but if you have a list that's so long it's hitting some limit, then that (even if it didn't hit the limit), would be a very inefficient way to do a query. I would create a temp table with the values and use the subquery instead, or even better, create the temp table with distinct values and do an inner join with it in your query. That will perform the best.

Tom

[flachboard84]

Thanks again, Tom! I can definitely do sub queries if that's the best option for me.

[sastpw]

Yep, that or the join. If you need any other help with this, just let me know!

Thanks,

Tom