topic cartesian product in SAS Programming

cartesian product

Steelers_In_DC — Fri, 23 Dec 2016 14:08:18 GMT

I have two tables with variables listed below. In the first table origacct number is meant to be original account number and should remain the same. acctnbr can change for several reasons but origacct should not change. During my initial research I have found this is not true. I am using the second table to search history, later I will be using a third but I don't currently have access.

I'll start with what I'm thinking and hopefully someone has a better solution. I will be doing a self join, cons_test = cons_test, joining acctnbr to origacct. Next I will join the three variables in the second table. I need to keep acctnbr, but join to each other variable, I will then sort by opendate to find the oldest account number associated with the current acctnbr. Currently the database doesn't have a good way to view customer history. Is an array the best way to accomplish this, I'm open to any suggestions.

data cons_test;
set consumer_agg(obs=10000);
keep acctnbr origacct opendate;
run;

data fdr_start;
set fdr_agg(obs=10000);
keep cracct xref1 xref2 xrefacct opendate;
run;

Re: cartesian product

Reeza — Fri, 23 Dec 2016 14:14:49 GMT

Create a master table of accounts to what account number they should be. This is most likely a recursive search, but it's been solved on here before several times. Ideally you don't use a Cartesian product, I think the best solutions were hash tables.

https://communities.sas.com/t5/Base-SAS-Programming/Recursive-lookup-for-ID-s/m-p/259635/highlight/true#M50238

Re: cartesian product

ballardw — Fri, 23 Dec 2016 15:10:26 GMT

One might hope there are additional pieces of information to identify a person or oganization to help decide if the values really are for the same person or typos. Also I might look at the earliest dates associated with any of the information combinations as well for determining a the "true" origacct value.