topic Analysis by row in SAS Procedures

Analysis by row

Q1983 — Fri, 11 Apr 2014 12:48:11 GMT

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

Re: Analysis by row

Vince28_Statcan — Fri, 11 Apr 2014 13:48:32 GMT

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

Re: Analysis by row

art297 — Fri, 11 Apr 2014 21:56:39 GMT

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;

Re: Analysis by row

WWW_BIGINFOANALYSIS_COM — Fri, 11 Apr 2014 22:47:17 GMT

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