Analysis by row

[Q1983]

Here is my scenario

Table1

Loan1            Loan2     Loan_Pos    Name

1111              1112            1             Geter

1112              1111            2             Geter   move this one to table 2

1113               1115           1             Snow

1116                                 2             Baldwin

Table 2

Loan1            Loan2     Loan_Pos    Name

1112              1111            2             Geter   move this one to table 2

Here is the issue.  Loan_Pos pertains to the loan position for loan1.  For example 1111 is in a 1st position and 1112 (same row) is the 2nd.  The second row has the same information except in reverse.  1112 is the second position and 1111 is the first.  Therefore the row = 2 becaise the second loan is in the left most position.  In that scenario I need for the loan to be placed in another table.  Now for 1116 it only has a second loan so it stays in the first table

What makes this tricky is you are analyzing 2 consecutive rows that are reversing loan numbers.  I hope this visual is sufficient to advise on how to accomplish this movement of the loan      

[Vince28_Statcan]

Well this may be a little simplistic but as I don't know the full extent of your data structure, here's how you can work it out:

step 1. Proc Sort by Name, Loan_Pos  /* name or whatever other unique client ID you have */

step 2. Data step using BY group on Name (or whatever other unique client ID you have */

     ...

      if first.name then output table1;

        else output table2;

run;      

If you can have significantly more than 2 loans per individuals and your problem is more complex than that, you would need to throw in an example of a "worst case scenario" where you want to distribute X records over Y files according to some logic like you've described.

Vince

[art297]

This may also be overly simplistic, but it sounds to me like you are trying to do something like:

data Table1;

  input Loan1 Loan2 Loan_Pos Name $;

  cards;

1111    1112   1  Geter

1112    1111   2  Geter

1111    1112   3  Geter

1113    1115   1  Snow

1113    1116   2  Snow

1116       .   2  Baldwin

;

data temp (drop=l1 l2);

  set table1 (rename=(loan1=l1 loan2=l2));

  loan1=min(l1,l2);

  loan2=ifn(missing(l1) or missing(l2),.,max(l1,l2));

run;

proc sort data=temp;

  by name loan1 loan2 loan_pos;

run;

data table2;

  set temp;

  by name loan1 loan2;

  if not(first.loan2);

run;

[WWW_BIGINFOANALYSIS_COM]

data Table1;

  infile datalines  missover;

  input Loan1 1-4 Loan2 6-9 Loan_Pos 11 Name $;

datalines;

1111 1112 1 Geter

1112 1111 2 Geter

1113 1115 1 Snow

1116      2 Baldwin

;

run;

proc sort data=Table1;

  by Name Loan_Pos;

run;

data Table2(drop=count);

  set Table1;

  by Name;

  if first.Name=1 then count=0;

  count+1;

  if count=2 then output;

run;

www.biginfoanalysis.com