WHERE CALUSE WITH MANY VALUES IN PROC SQL

[moxsh]

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?

[Tom]

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.  

[moxsh]

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

[Ksharp]

As Tom said :

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

[ballardw]

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;

[moxsh]

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

[LinusH]

Then change to inner join instead.

Will probably perform better as well.

[andreas_lds]

---

@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