topic Re: Help with filtering data in New SAS User

Help with filtering data

marleeakerson — Thu, 05 Nov 2020 20:55:21 GMT

Hello,

Using this sample data:

ID Code

1 A

1 B

1 C

2 A

2 B

3 A

3 B

3 C

I am trying to create a new table that pulls observations where an ID has both Code 'A' and Code 'C' associated with it.

So the final data would look like:

ID Code

1 A

1 B

1 C

3 A

3 B

3 C

Thank you!

Re: Help with filtering data

Kurt_Bremser — Thu, 05 Nov 2020 21:19:07 GMT

Run a double DO loop, since it is sorted:

data want;
do until (last.id);
  set have;
  by id;
  if code = 'A' then _a = 1;
  if code = 'C' then _c = 1;
end;
do until (last.id);
  set have;
  by id;
  if _a and _c then output;
end;
drop _:;
run;

Re: Help with filtering data

novinosrin — Thu, 05 Nov 2020 22:24:58 GMT

Hi @marleeakerson If you wanna try the PROC SQL aka "Readymeals" solution, it's worth having fun.

data have;
 input ID         Code $;
 cards;
1              A

1              B

1              C

2              A

2              B
2			   A

3              A

3              B

3              C
;



proc sql;
 create table want as
 select *
 from have
 group by id
 having count(distinct ifc(code in ('A','C'),code,' '))=2
 order by id,code;
quit;

Sir @ChrisHemedinger How on earth you folks could be so genius and creative to think of IFC/IFN making it too user friendly(aka readymeals) to a fault. Kudos!!!*1E6. Just an incredible thought to envision it in the first place. If the designer would accept an ordinary bloke's appreciation, please let him/her know the privileges we enjoy.

Re: Help with filtering data

FreelanceReinh — Thu, 05 Nov 2020 23:16:20 GMT

Hi @novinosrin: I think the condition

having max(code='A') & max(code='C')

(or with SUM instead of MAX) would improve performance. (At least it did on my computer with 5.5 million obs. in HAVE of which 48% were selected: It was more than three times faster, but still much slower [about factor 2] than the double DOW loop.)

Re: Help with filtering data

novinosrin — Fri, 06 Nov 2020 00:34:36 GMT

Yes I agree. Performance being the criteria, DOW is indeed the best. It's just that I find some of the Proc SQL functionality very trendy and user-friendly while being seated among a group of general business users. They just enjoy fastfood and done. 🙂 Even beginners seem to grasp so quickly. I guess that accomplishes much of our credit risk queries at ease.

Re: Help with filtering data

ChrisHemedinger — Fri, 06 Nov 2020 12:33:13 GMT

Love to see the different creative solutions here.

IFC (and IFN) is a SAS function, not native to SQL. Sometimes we need a SAS function to do the job -- there are so many available and SQL doesn't have analogous functions for all of them. But you might pay a performance penalty for using a SAS function in PROC SQL especially when working with database data. SAS tries to push as much work to the database as possible, but a unique SAS function will force SAS to bring at least some of the data over and increase the I/O cost.