Previous Row Count

[Q1983]

data test(keep=ln  borrower);

set test2;

if borrower =1 then do; if lag(borrower)then filter=1;end;

run;

ln                                     borrower              filter

1111                                    1

1111                                    2

2222                                   1                       1

3333                                   1         

3333                                  2

The desired results is to populate filter with a 2 if the first instance is a 1 and the second instance is a 2

so for example     

ln                                     borrower              filter

1111                                    1                        2

1111                                    1                        2

2222                                   1                       1

3333                                   1                       2

3333                                  2                       2

I thought lag function would do this,  Any ideas

[Astounding]

You will need to define a lot more combinations than that.  What if there is only one observation in a group, but BORROWER is 2?  What if there are two observations in a group, the sequence of BORROWER values is 2 then 1?  What if there are more than 2 observations in a group?

Your suspicions are correct ... LAG is not the right tool for the job.  But more important is to spell out more details of what the result needs to be.

[DanielSantos]

Not that clear what you are lookin for.

From the example you gave, it just looks FILTER is a counter grouped by IN, and borrower really hasn't nothing to do with it.

For this, the following will produce the same:

proc sql noprint;
create table WANT as
select a.*, b.FILTER from HAVE as a
left join
(select IN, count(*) as FILTER from HAVE group by IN) as b
on a.IN = b.IN;
quit;

If you want to assign FILTER=2 for INs that should have borrower=1 AND borrower=2 (which is not what the expected result shows) and assuming borrower values could be only 1 or 2, just add DISTINCT like above:

proc sql noprint;
create table want as
select a.*, b.FILTER from have as a
left join
(select DISTINCT IN, count(*) as FILTER from have group by IN) as b
on a.IN = b.IN;
quit;

Daniel Santos @ www.cgd.pt