Proc SQL

[ankitpal064]

Hi Team,

 i need to pull some data from DATABASE for some about 50000 participants.ppts unique number is present in file which is in char format in database.

code :

data abc;

infile abcsajk;

input @001  unique $9.

line = " ' " !! unique !! " ' "

run;

%let ppts; creating macro vraiable 

proc sql;

select unique : line

into ppts

from abc;

quit;

run;

proc sql;

database connection;

create table select * from connectio to database(

select * from db.table where unique_id in (&ppts)

Problem here is when ppts get resolved statement length get increased then it's length and query do not run.

So as per me solution could be if i can use sas dataset in subquery like below

proc sql;

database connection;

create table select * from connectio to database(

select * from db.table where unique_id in (select line from WORK.abc)

but its not working ,

Can anyone help or tell me its possible or not.whether we can uses sas dataset in subquery

[SASKiwi]

Yes it is possible but you need to upload the SAS table into the database first so both tables are in the same environment. The best way is to create a temporary database table containing the SAS data you wish to use for subsetting.

What database are you using and does your user name have the database permissions to create a temporary table? You may need to confirm this with a DBA.

[ballardw]

If I understand your issue is that the variable Line is too long. So specify the maximum length before use:

data abc;
infile abcsajk;
input @001  unique $9. ;
length line $ 15;
line = " ' " !! unique !! " ' ";

run;

Also if you do not want spaces in line then consider

line = quote(unique);

Did actually want to insert spaces before and after each ' in the value of line? If not either quote as above or use length 11 and "'" instead of the " ' ". The later may be easier to read but is adding characters to the variable.

[SASKiwi]

Also I agree that building a WHERE expression in your case is not going to work with 50,000 unique IDs as the size limit for a macro variable is 64K. Uploading a SAS table is the preferrable option.