Linking accounts through a rolling table   I have a table of accounts when they are lost and get replaced and it looks like this   new   old 123   654 789   390 944   789 801   552 002   944   When I have account characteristics I need to sum I need to chain together the accounts - for example the original account number is 390 and then it became 789, which became 944, which became 002.  For that account I would need to sum transactions for all 4 cards as they are essentially the same card.   I was able to get this to work, but for a 40,000 card population it took about 10 minutes to run, and now I'm working with over 1,000,000 accounts.  I'm wondering if a data step would be more efficient?  I did it via proc sql (after testing that nothing had been replaced more than 4 times)   First I created a table of account relationships using multiple fields and then used an or statement in a later table where I joined on a.acct = b.account or a.acct = b.replace1 or a.acct = b.replace2 or a.acct = b.replace3, etc.   Like I mentioned it worked, but it took awhile to run and that was with a small population.  Any ideas on a more efficient way?   proc sql; create table account_list as select c.account, h1.new_account as replace1, h2.new_account as replace2 h3.new_account as replace3, h4.new_account as replace4 from accounts as c left join replace_table as h1 on c.account = h1.old_acct left join replace_table as h2 on h1.new_account = h2.old_account left join replace_table as h3 on h2.new_account = h3.old_account left join replace_table as h4 on h3.new_account = h4.old_account ;quit;     ... View more

This is killing me, if someone can help I'd be exceedingly grateful.   I have this program snippet.  In the last proc sql I am getting an error with the date.  Why won't it take my date variable????   Please help?  Error message followed by code.   NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements. NOTE: Line generated by the macro variable "DAYDATE1". 18 24NOV2016 _______ 22 76 ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, -, /, <, <=, <>, =, >, >=, AND, EQ, EQT, GE, GET, GT, GTT, LE, LET, LT, LTT, NE, NET, OR, ^=, |, ||, ~=. ERROR 76-322: Syntax error, statement will be ignored.       data dates; daydate1=intnx('day',today(),-1,'B'); FORMAT daydate1 date9.; run;   proc sql; select daydate1 into :daydate1 FROM dates; quit;   proc sql; connect to sqlsvr as sqlsvr (dsn=sqlserverp authdomain=sqlauthp); create table work.submit as select * from connection to sqlsvr (select ID, store_number, min(rpt_dt) as Submit_Dt from Appstable group by ID, store_number order by ID asc); quit;   proc sql; create table subday as select input(store_number,4.) as store, count(ID) as Submits from submit where submit_dt = &daydate1 group by store_number; quit; ... View more