BookmarkSubscribeRSS Feed
Q1983
Lapis Lazuli | Level 10

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

2 REPLIES 2
Astounding
PROC Star

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
Barite | Level 11

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

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 634 views
  • 0 likes
  • 3 in conversation