Proc sql

[Sanjeevas]

data a;
input party account$ balance;
datalines;
1 A1 50
1 A2 60
1 A3 30
2 A4 40
2 A5 50
3 A6 30
3 A7 90
run;

write a query to fetch all accounts listed to the party who has at least one account having balnce greater than 50?

[Patrick]

Welcome to the SAS Communities!

Your question feels like homework/an exercise. What have you tried so far? Where do you get stuck?

Post the code you've already developed even if not fully working yet.

[SASKiwi]

A sub-query is a good way of doing this type of question:

proc sql;
  create table want as 
  select A.*
  from A as A
  inner join
  (select party
         ,count(*) as Accounts_Count
   from A
   where balance > 50
   group by party
  ) as B
 on A.party = B.party;
quit;

[Sanjeevas]

Thanks.!!

[AndreaVianello]

 why  calculate  count(*) as Accounts_Count... ?

proc sql;
  create table want as 
  select A.*
  from A as A
  inner join
      (select distinct party  from A
       where balance > 50
      ) as B
 on A.party = B.party;
quit;

[Ksharp]

data a;
input party account$ balance;
datalines;
1 A1 50
1 A2 60
1 A3 30
2 A4 40
2 A5 50
3 A6 30
3 A7 90
;

proc sql;
create table want as
select * from a where party in
 (select party from a where balance>50) ;
quit;

[AndreaVianello]

perfect !