DATA Step, Macro, Functions and more

Previous Row Count

Reply
Frequent Contributor
Posts: 143

Previous Row Count

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

Super User
Posts: 5,516

Re: Previous Row Count

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.

Super Contributor
Posts: 474

Re: Previous Row Count

[ Edited ]

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

 

Ask a Question
Discussion stats
  • 2 replies
  • 126 views
  • 0 likes
  • 3 in conversation