Using a macro var list within proc sql

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 121
Accepted Solution

Using a macro var list within proc sql

I have created the macro variable &LoanID. using the sas sql code below (Exhibit 1). The macro variable &LoanID. contains a string of loan ID values, which look like the ones in Exhibit 2. I need to use another proc sql (passthrough) query to pull data fields that I can ascribe to the values of &LoanID and I am trying to run the code in Exhibit 3 to accomplish this. My only problem is that the code in Exhibit 3 only picks the top value from the list in Exhibit 2 and ignores the remaining values. How can I have it such that all values corresponding to &LoanID get read? Any insight would be extremely appreciated; thanks in advance.

 

Exhibit 1                                               Exhibit 2

proc sql;                                                &LoanID.

select LoanID                                         43216

into :LoanID                                            39871

from dataset;                                          32758

                                                                 etc

 

Exhibit 3

select LoanNumber, LoanTerm, LoanBalance

from InternalDatabaseTable1

where LoanNumber=&LoanID.; 

 

 

 

 

 

 

 

 


Accepted Solutions
Solution
‎10-20-2016 02:20 PM
Super User
Posts: 7,405

Re: Using a macro var list within proc sql

Try

proc sql;
select LoanID
into :loanid separated by ','
from dataset;
select LoanNumber, LoanTerm, LoanBalance
from InternalDatabaseTable1
where LoanNumber in (&loanid.);
quit;

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Super Contributor
Posts: 339

Re: Using a macro var list within proc sql

You have to change 1. select LoanID into :LoanID Separated By ', ' From Dataset;
and 2. Where LoanNumer in (&LoadID.)

Solution
‎10-20-2016 02:20 PM
Super User
Posts: 7,405

Re: Using a macro var list within proc sql

Try

proc sql;
select LoanID
into :loanid separated by ','
from dataset;
select LoanNumber, LoanTerm, LoanBalance
from InternalDatabaseTable1
where LoanNumber in (&loanid.);
quit;

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 19,057

Re: Using a macro var list within proc sql

Super User
Super User
Posts: 7,695

Re: Using a macro var list within proc sql

Why?  Your just going to cause yourself a massive headache.  SQL has the ability to sub-query native to the language, there is absolutely no need to go down this route simply:

proc sql;
  select  LOANNUMBER, 
          LOANTERM, 
          LOANBALANCE
  from    INTERNALDATABASETABLE1
  where   LOANNUMBER in (select distinct LOANID from DATASET); 
quit;

So much simpler.  

Frequent Contributor
Posts: 121

Re: Using a macro var list within proc sql

RW9; thanks. I went that route and agree that it's far simpler, however, my mother-query (for lack of a better term) is a passthough query, so the subquery that you and I thought of gives me an error. Using a macro variable was my next best solution; if you have a way to work past that error, I would be more than happy to give it a shot. 

Super User
Posts: 19,057

Re: Using a macro var list within proc sql

Create a table from your 'mother query' and then create the macro variable locally in a non pass through step, if possible.

Frequent Contributor
Posts: 121

Re: Using a macro var list within proc sql

Reeza, many thanks for offering your thoughts. I tried your suggestion before my original post as well; this particular route doesn't seem feasible because the 'mother table' becomes too large and takes WAY too long to create. Macro variable seems like the best way to go; in any case, I have resolved this issue, which caused yet another, however, because it turns out that the list of values contained within my macro variable (&LoanID.) is way too long. If you have code for breaking down the macro variable list into smaller chunks; I was thinking of going with somekind of loop.

 

proc sql;

select LoanID

into :LoanID separated by ','

from InternalTable;

 

Super User
Super User
Posts: 7,695

Re: Using a macro var list within proc sql

If you are passing through the query then you need to have all the data in one place.  What I would recommend is to create the list of id's in your user area on the database, then you can use that in the subquery (and delete it afterwards if needed).  Something like:

proc sql;
  connect to db (...);
  execute by db ('create table user$temp as select disitnct ID from sasdata;');
  create table WANT as
  select * from connection to db (
    select * from DATABASE_DATA
    where ID in (select ID from USER$TEMP);
  execute by db ('drop table user$temp');
  disconnect from db;
quit;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 506 views
  • 2 likes
  • 5 in conversation