topic Proc SQL in SAS Programming

Proc SQL

ankitpal064 — Tue, 15 Sep 2015 04:57:29 GMT

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

Re: Proc SQL

SASKiwi — Tue, 15 Sep 2015 05:30:04 GMT

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.

Re: Proc SQL

ballardw — Tue, 15 Sep 2015 15:34:53 GMT

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.

Re: Proc SQL

SASKiwi — Tue, 15 Sep 2015 20:12:04 GMT

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.