topic Re: Theoretical merging question in SAS Data Management

Theoretical merging question

ilikesas — Sun, 21 Dec 2014 17:48:29 GMT

Hi,

I have 2 tables, one has 76 000 rows and the other 22 000 rows, and I want to merge them according to a certain criteria.

I read that regardless of the merging criteria that I set, SAS will always multiply the tables in its calculation, and in this case the multiplication is equal to 1.6 Billion.

The reason why I am asking this question is because I ran the code an hour ago and its still thinking.

Can there be a shortcut?

Thank you

Re: Theoretical merging question

LinusH — Sun, 21 Dec 2014 18:46:53 GMT

Your question is not very precise.

If you are using data step merge by, it does not multiply. It does more of a roe by row merge.

SQL on the other, does "logically" a Cartesian product, and applies any where/on clause. But in real world, the SQL optimizer/planner does prevent the total product to be created.

Share your code and sample data for more precise answers.

Re: Theoretical merging question

ilikesas — Sun, 21 Dec 2014 19:53:30 GMT

Hi LinusH,

I already asked this question on the community blog

https://communities.sas.com/message/240300#240300

there I have a small sample and community members gave me an answer that works good for my small sample, but with the real data that I have it takes me already 3 hrs

I guess it is better to go back to the link of my initial question

Re: Theoretical merging question

Patrick — Mon, 22 Dec 2014 02:01:33 GMT

Based on the information as provided from your related question under: https://communities.sas.com/message/240300#240300

Given that the look-up table (EoY data) should fit into a hash we could take such an approach which should perform quite well. In below code I'm not 100% sure if I've got the calculation of the closest EoY date correct so you might need to amend this.

data daily_data;

infile cards dlm=',';

input date :ddmmyy10. ticker $;

format date ddmmyy10.;

cards;

12/12/2008,ABC

4/6/2008,ABC

25/3/2010,DEF

6/2/2002,DEF

;

run;

data eoy_data;

infile cards dlm=',';

input date :ddmmyy10. ticker $;

format date ddmmyy10.;

eoy_amt=_n_;

cards;

1/12/2010,ABC

1/12/2009,ABC

1/12/2008,ABC

1/12/2007,ABC

1/12/2006,ABC

1/12/2010,DEF

1/12/2009,DEF

1/12/2008,DEF

1/12/2007,DEF

1/12/2001,DEF

;

run;

data want;

set daily_data;

if _n_=1 then

do;

if 0 then set eoy_data(rename=(date=eoy_date));

dcl hash h1(dataset:'eoy_data(rename=(date=eoy_date))');

_rc=h1.defineKey('ticker','eoy_date');

_rc=h1.defineData(all:'y');

_rc=h1.defineDone();

end;

/** find closest 1 Dec<yyyy> date **/

format _match_date ddmmyy10.;

if abs(intnx('year',date,-1,'e') - date) <= abs(intnx('year.12',date,0,'e') - date) then

_match_date=intnx('year.12',date,0,'b');

else

_match_date=intnx('year.12',date,1,'b');

/* look up eoy data */

_rc=h1.find(key:ticker, key:_match_date);

run;

Message was edited by: Patrick Matter Oh, I believe I get it now. Your EoY data has a date of 1 December <yyyy> but this means like so often "month starting on...". The actual data is from the end of the month (the year) and that's where you want the date difference to be calculated from. I've amended the code accordingly.