topic Re: Proc sql in SAS Programming

Proc sql

Sanjeevas — Wed, 05 Jan 2022 06:58:50 GMT

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?

Re: Proc sql

Patrick — Wed, 05 Jan 2022 07:15:35 GMT

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.

Re: Proc sql

SASKiwi — Wed, 05 Jan 2022 07:21:12 GMT

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;

Re: Proc sql

Sanjeevas — Wed, 05 Jan 2022 07:25:49 GMT

Thanks.!!

Re: Proc sql

AndreaVianello — Wed, 05 Jan 2022 09:12:24 GMT

 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;

Re: Proc sql

Ksharp — Wed, 05 Jan 2022 11:34:48 GMT

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;

Re: Proc sql

AndreaVianello — Wed, 05 Jan 2022 12:35:21 GMT

perfect !