topic Re: I'm getting a 79-322 Error idk where it's coming from in SAS Programming

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

aasdfafafsdfsaf — Wed, 30 Mar 2022 00:57:25 GMT

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?

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

SASKiwi — Wed, 30 Mar 2022 01:08:43 GMT

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.

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

aasdfafafsdfsaf — Wed, 30 Mar 2022 01:12:19 GMT

are spaces characters

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

SASKiwi — Wed, 30 Mar 2022 01:19:01 GMT

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

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

Tom — Wed, 30 Mar 2022 03:26:46 GMT

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.

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

aasdfafafsdfsaf — Wed, 30 Mar 2022 04:31:42 GMT

Hi Tom,

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

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

SASKiwi — Wed, 30 Mar 2022 05:30:45 GMT

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.

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

Kurt_Bremser — Wed, 30 Mar 2022 09:44:44 GMT

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;