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
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
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;
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.