Breaking down a large macro var list

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 121
Accepted Solution

Breaking down a large macro var list

I have the code below; Mod1 contains ~60,000 observations (Step 1) which I need to read into macro-variable &LoanID (Step 2). Eventually, I want to ascribe a bunch of attributes onto the values of &LoanID. (Step 3). My problem is that the list contained within &LoanID. is huge and exceeds certain limits, thereby generating an error. To remedy this error, I have resorted to breaking down Mod1 6000 observations at a time (Step 1) which is not a very efficient alternative. Does anyone have thoughts on a better alternative?

 

Step 1:

data Mod2;

set Mod1 (obs=6000);

run;

 

Step 2:

proc sql;

select LoanID

into :LoanID separated by ','

from Mod2;

 

Step 3:

proc sql;

create table as Mod3 

select Loan_ID, LoanTerm, LoanBalance

from InternalTable where LoanID in (&LoanID.);

 

&LoanID.

13623

54379

43256

43266

54522

etc.


Accepted Solutions
Solution
‎01-30-2017 03:36 PM
Respected Advisor
Posts: 4,137

Re: Breaking down a large macro var list

There are SAS/Access options which allow you to influence how and where heterogenous joins get processed.

http://support.sas.com/documentation/cdl/en/acreldb/69039/HTML/default/viewer.htm#n0tcetvx1zpnayn1r8...

 

proc sql;
  create table as Mod3 
    select Loan_ID, LoanTerm, LoanBalance
      from 
        <DB table big>(dbmaster=yes) as B, 
        <SAS table small> as S
      where B.LoanID=S.LoanID
    ;
quit;

 

I believe if you use dbmaster=yes as in the above code, then the query which gets sent to the DB is pretty similar to what you're trying to generate "manually".

View solution in original post


All Replies
Respected Advisor
Posts: 3,156

Re: Breaking down a large macro var list

I don't see the benefit of using Macro varaible for look-up purpose. There are many other 'better' ways available, to name a few, for the same outcome you are seeking, you could use SQL inner join / subquery, Data step merge / Hash table/index.

Frequent Contributor
Posts: 121

Re: Breaking down a large macro var list

Thanks for offering your thughts; my query is actually a passthrough query. As a result, doing smth like a subquery gives me an error; I have tried all non-macro ways I know and have found that macro var list is the best solution. The only problem is that the list is very long, so I am trying to work around that.

Super User
Posts: 5,370

Re: Breaking down a large macro var list

My SQL isn't the greatest, but I think you can skip the 6,000-observation chunks if you do something like this and skip the macro language:

 

proc sql;

create table as Mod3 

select Loan_ID, LoanTerm, LoanBalance

from InternalTable where LoanID in (select distinct LoanID from Mod1);

quit;

Frequent Contributor
Posts: 121

Re: Breaking down a large macro var list

Thanks for offering your thoughts; my query is actually a passthrough query. As a result, doing smth like a subquery gives me an error; I have tried all non-macro ways I know and have found that macro var list is the best solution. The only problem is that the list is very long, so I am trying to work around that.

Super User
Posts: 5,370

Re: Breaking down a large macro var list

Most pass-through systems will allow you to perform a subquery.  Is it not possible to load MOD1 to the box you are passing through to?  It's only 60K rows and one field that need to get loaded.

Super User
Posts: 7,462

Re: Breaking down a large macro var list

Why go through all this hassle? Sort and merge by loanid.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 121

Re: Breaking down a large macro var list

Thanks for offering your thoughts; my query is actually a passthrough query. I have tried sorting and merging, however, the dataset that I would generate using the proc sql query and which I, in turn, would merge with my LoanIDs is just WAY too large; my code doesn't converge after 40mins. I am trying to find smth more efficient.

Super User
Posts: 7,462

Re: Breaking down a large macro var list


maroulator wrote:

Thanks for offering your thoughts; my query is actually a passthrough query. I have tried sorting and merging, however, the dataset that I would generate using the proc sql query and which I, in turn, would merge with my LoanIDs is just WAY too large; my code doesn't converge after 40mins. I am trying to find smth more efficient.


Then transfer the list to a table in the DBMS and you can do the subqery there. Or pull all data into SAS and do everything in SAS.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Solution
‎01-30-2017 03:36 PM
Respected Advisor
Posts: 4,137

Re: Breaking down a large macro var list

There are SAS/Access options which allow you to influence how and where heterogenous joins get processed.

http://support.sas.com/documentation/cdl/en/acreldb/69039/HTML/default/viewer.htm#n0tcetvx1zpnayn1r8...

 

proc sql;
  create table as Mod3 
    select Loan_ID, LoanTerm, LoanBalance
      from 
        <DB table big>(dbmaster=yes) as B, 
        <SAS table small> as S
      where B.LoanID=S.LoanID
    ;
quit;

 

I believe if you use dbmaster=yes as in the above code, then the query which gets sent to the DB is pretty similar to what you're trying to generate "manually".

Valued Guide
Posts: 632

Re: Breaking down a large macro var list

putting aside the 'Why'. 

 

You may want to consider a list of macro variables as opposed to a single macro variable.  In the following each LoadId is written to a separate macro variable.  The number of macro variables is limited only by available memory, not the 64K rule.  I have also modified your code a bit, but this should get you started.

 

data Mod2(keep=loanid)
     InternalTable(keep=loanid loanterm loanbalance);
input loanid $ loanterm loanbalance;
datalines;
1234 1 2
2345 2 3
3456 3 4
4567 4 5
run;

%macro tryit;
%local idcount i;
proc sql;
select distinct LoanID
   into :LoanID1 -
      from Mod2;
%let idcount = &sqlobs;

proc sql;
create table Mod3 as
   select LoanID, LoanTerm, LoanBalance
      from InternalTable
         where LoanID in (
            %do i = 1 %to &idcount;
               %if &i ne 1 %then ,;
               "&&LoanID&i"
            %end;
                          );
quit;
%mend tryit;
%tryit

☑ This topic is solved.

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

Discussion stats
  • 10 replies
  • 546 views
  • 3 likes
  • 6 in conversation