I'm getting a 79-322 Error idk where it's coming from

[aasdfafafsdfsaf]

I'm working with two large datasets.

ive read somewhere online that select into can work to reduce load on servers and improve runtime.

proc sql noprint;
select quote(put(col1,4.)) 
into 
:my_list separated by ","
from my_data
;quit;

%put &my_list;

proc sql;
create table want 
as select col1,col2 
from very_big_table
where col3 in (&my_list.)
;quit;

after running the second chunk of code i get my 79-322 error. Expecting a ). I'm really confused this looks proper to me but why is it giving this error?

[SASKiwi]

There's no evidence in your posted code that matches your error message. You need to post your actual SAS log so we can see what is really going on. One thing to check is the length of the my_list macro variable - it can be no more than 32K characters long.  

[aasdfafafsdfsaf]

are spaces characters

[SASKiwi]

Yes, if they are within the long string. Add the STRIP function to remove unnecessary spaces.

[Tom]

So COL1 is numeric with values between -999 and 9999?

And COL3 is character with a length of $4?  And the values of COL3 that use less than 4 characters are right aligned?

How many observations are in MY_DATA?  Since you are writing 7 characters per value you can only fit 64K/7 -> 9,362 observations worth of values into the macro variable before the value will be truncated.

Do any of the values of COL1 in MY_DATA repeat?  You should add the DISTINCT keyword to your SQL SELECT statement as you only need one copy of each value in the macro variable.

[aasdfafafsdfsaf]

Hi Tom,

Thanks for the help. It ended up being too long and exceeded the character limit.

[SASKiwi]

Your best option is to load the SAS table into a DBMS temporary table and then join to the DBMS table from there to subset your data.

[Kurt_Bremser]

I am very sure that the data from your first query exceeds the maximum length of a macro variable (64K).

Run this instead:

proc sql;
create table want as
  select t2.col1, t2.col2
  from my_data t1 left join very_big_table t2
  on t1.col1 = t2.col3
;
quit;

or (probably fastest)

data want;
set very_big_table (keep=col3 col1 col2);
if _n_ =1
then do;
  declare hash my (dataset:"mydata (keep=col1 rename=(col1=col3))");
  my.definekey("col3");
  my.definedata("col3");
  my.definedone();
end;
if my.check() = 0;
drop col3;
run;