DATA Step, Macro, Functions and more

Proc SQL

Reply
Occasional Contributor
Posts: 7

Proc SQL

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

 

 

Super User
Posts: 3,101

Re: Proc SQL

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.

Super User
Posts: 10,466

Re: Proc SQL

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.

Super User
Posts: 3,101

Re: Proc SQL

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.

Ask a Question
Discussion stats
  • 3 replies
  • 163 views
  • 1 like
  • 3 in conversation