topic WHERE CALUSE WITH MANY VALUES IN PROC SQL in SAS Programming

WHERE CALUSE WITH MANY VALUES IN PROC SQL

moxsh — Tue, 08 Mar 2022 21:10:36 GMT

Hi All,

I am trying to run a query from the database where I am specifying some accounts, However the accounts I want to specify in the where clause are more than 30,000 so I get the error message

"Submitted code lines longer than 6,000 characters,SAS is unable to process lines longer than 6,000 characters" Is there any way around this?

Re: WHERE CALUSE WITH MANY VALUES IN PROC SQL

Tom — Tue, 08 Mar 2022 21:53:21 GMT

Are the account names really over 6000 characters long?

If not try inserted some line breaks into your program file so that the lines are not so long.

If you expect an actual human to be able to read and understand the file limit the length of the lines to 80 characters or less.

Re: WHERE CALUSE WITH MANY VALUES IN PROC SQL

ballardw — Tue, 08 Mar 2022 22:36:09 GMT

Are you going to admit that perhaps you created a macro variable to do this?

I may be easier, and certainly won't have that character limit by using a JOIN on a data set that contains the account numbers.

Generic example:

Proc sql;
   create table want as 
   select b.* 
   from accountstokeep as a
           left join
           otherdataset as b
          on a.accountnumber=b.accountnumber
  ;
quit;

Re: WHERE CALUSE WITH MANY VALUES IN PROC SQL

moxsh — Wed, 09 Mar 2022 05:37:29 GMT

I tried this but it still writes accounts that dont have anything to return on the "want" dataset which sorts of takes some time to execute

Re: WHERE CALUSE WITH MANY VALUES IN PROC SQL

moxsh — Wed, 09 Mar 2022 05:41:41 GMT

Its a list of many accounts,so on the SAS Editor they exceed 6,000 characters by far

Re: WHERE CALUSE WITH MANY VALUES IN PROC SQL

LinusH — Wed, 09 Mar 2022 07:22:44 GMT

Then change to inner join instead.

Will probably perform better as well.

Re: WHERE CALUSE WITH MANY VALUES IN PROC SQL

Ksharp — Wed, 09 Mar 2022 12:24:47 GMT

As Tom said :

If not try inserted some line breaks into your program file so that the lines are not so long.

Re: WHERE CALUSE WITH MANY VALUES IN PROC SQL

andreas_lds — Wed, 09 Mar 2022 12:38:36 GMT

@moxsh wrote:

Hi All,

I am trying to run a query from the database where I am specifying some accounts, However the accounts I want to specify in the where clause are more than 30,000 so I get the error message

"Submitted code lines longer than 6,000 characters,SAS is unable to process lines longer than 6,000 characters" Is there any way around this?

Looks like something went wrong in the design-phase. Those accounts should be in a dataset, so that you could use a join to get the required output. See first answer by @ballardw