Selecting records

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 122
Accepted Solution

Selecting records

My data looks like this:

 

LoanId      Mod_Flag   

12345

12345    

12345           Y

34521     

34521

68749           Y

68749           Y

68749          

78423

78423

 

Is there any way that I could select a) all records corresponding to those LoanIds that have a blank (ie. non-Y) mod_flag throughout the LoanId group, in addition to b) the records from those LoanId groups whose mod_flag may either be blank or mod_flag='Y', corresponding to those LoanIds that ONLY have a mod_flag = 'Y'? To be clearer by final data would look as follows: 

 

LoanId      Mod_Flag   

12345           Y

34521     

34521

68749           Y

68749           Y    

78423

78423

 

 

 

 


Accepted Solutions
Solution
‎09-25-2015 06:23 AM
Respected Advisor
Posts: 3,799

Re: Selecting records

Posted in reply to maroulator

I think a "self merge" will work nicely here.

data loan;
   infile cards firstobs=2 missover;
   input loanid :$5. mod_flag:$1.;
cards;
LoanId      Mod_Flag   
12345
12345    
12345           Y
34521     
34521
68749           Y
68749           Y
68749          
78423
78423
;;;;
   run;
proc print;
   run;
data loan2;
   merge loan loan(in=in2 rename=(mod_flag=Y) where=(Y='Y'));
   by loanid;
   if in2 and mod_flag eq 'Y' then output;
   else if not in2 then output;
   drop y;
   run;
proc print;
   run;

Capture.PNG

View solution in original post


All Replies
Frequent Contributor
Posts: 144

Re: Selecting records

[ Edited ]
Posted in reply to maroulator

For the a) the first solution that come to my mind is to select those who have "Y"

 

proc sort data=have;

  by loanld mod_flag;

run;

 

data want;

set have;

by loanld mod_flag;

where mod_flag='Y';

if last.mod_flag; /*avoid duplicated loanld if multiple 'Y'*/

rename mod_flag=group_flag;

run;

 

data have;

merge have

           want;

if group_flag ne 'Y';

run;

 

For the b), with the original dataset have you could do something like

 

data have;

merge have

           want;

if group_flag eq Mod_flag;

run;

Trusted Advisor
Posts: 1,137

Re: Selecting records

Posted in reply to maroulator
proc sort data=have;
by LoanId descending Mod_Flag;
run;

data want(where=(Mod_Flag='Y' or new='N'));
set have;
by LoanId descending Mod_Flag;
retain new;
if first.LoanId then new='';
if Mod_Flag='Y' then new='Y';
if new='' then new='N';
run;
Thanks,
Jag
Solution
‎09-25-2015 06:23 AM
Respected Advisor
Posts: 3,799

Re: Selecting records

Posted in reply to maroulator

I think a "self merge" will work nicely here.

data loan;
   infile cards firstobs=2 missover;
   input loanid :$5. mod_flag:$1.;
cards;
LoanId      Mod_Flag   
12345
12345    
12345           Y
34521     
34521
68749           Y
68749           Y
68749          
78423
78423
;;;;
   run;
proc print;
   run;
data loan2;
   merge loan loan(in=in2 rename=(mod_flag=Y) where=(Y='Y'));
   by loanid;
   if in2 and mod_flag eq 'Y' then output;
   else if not in2 then output;
   drop y;
   run;
proc print;
   run;

Capture.PNG

Super User
Posts: 10,044

Re: Selecting records

Posted in reply to maroulator
data loan;
   infile cards firstobs=2 missover;
   input loanid :$5. mod_flag:$1.;
cards;
LoanId      Mod_Flag   
12345
12345    
12345           Y
34521     
34521
68749           Y
68749           Y
68749          
78423
78423
;;;;
   run;
proc sql;
create table want as
 select * from loan group by loanid having sum(Mod_Flag='Y')=0
 union all
 select * from loan where Mod_Flag='Y'
 order by loanid;
quit;

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 435 views
  • 2 likes
  • 5 in conversation