topic Re: Previous Row Count in SAS Programming

Previous Row Count

Q1983 — Mon, 06 Feb 2017 16:30:18 GMT

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

2222 1 1

3333 1 2

3333 2 2

I thought lag function would do this, Any ideas

Re: Previous Row Count

Astounding — Mon, 06 Feb 2017 16:53:12 GMT

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.

Re: Previous Row Count

DanielSantos — Mon, 06 Feb 2017 17:27:29 GMT

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