BookmarkSubscribeRSS Feed
Maheshvaran
Calcite | Level 5

Hi All,

Hope someone can help ...

I have the following hypothetical dataset:

MainAccountClientNumAlternativeAccount
AA1A1
AA1A2
AA1A3
AA2A4
AA2A3
BB1B1
BB1B2
CC1

You will note that main account AA has two clients linked to it and accounts BB and CC have one client linked to each. Account CC has no alternative accounts

In account AA, each of the clients have different alternative accounts, but they share 1 (A3)

I want to count how many alternative accounts each client has and how many alternatve account each main account has (without double counting A3 for the first account). The problem is counting only the distinct alternative account numbers when looking at a main account level. I tried using a count(case ...) statement but it will always double count the A3 account and i cannot use a nested statement to say only count it once when is is not distinct ...

My ideal final dataset needs to like like follow: (that is: NumAccount_AltAccounts for Account AA must show 4 not 5)

MainAccountClientNumNumClient_AltAccountsNumAccount_AltAccounts
AA134
AA224
BB122
CC100

Thanks!!

4 REPLIES 4
Tom
Super User Tom
Super User

Key idea is to use COUNT(DISTINCT varname) to get the counts.

Then you can create two queries that group by different variables and merge the results to get your final table.

I shortened the variable names to make it easier to type.

data have ;

  input (a b c) ($) @@;

cards;

AA 1 A1 AA 1 A2 AA 1 A3 AA 2 A4 AA 2 A3 BB 1 B1 BB 1 B2 CC 1 .

;

proc sql ;

create table want as

  select x.*,y.cnt2

  from

   (select distinct a,b,count(distinct c) as cnt1 from have group by a,b) x

  ,(select distinct a,count(distinct c) as cnt2 from have group by a) y

  where x.a = y.a

  order by 1,2

;

quit;

data _null_;

  set want;

  put (_ALL_) (=);

run;

a=AA b=1 cnt1=3 cnt2=4

a=AA b=2 cnt1=2 cnt2=4

a=BB b=1 cnt1=2 cnt2=2

a=CC b=1 cnt1=0 cnt2=0

Haikuo
Onyx | Level 15

Tom's SQL is no doubt the most pithy approach, here is a data step using 2XDOW:

data have;

infile cards truncover;

input (MainAccount ClientNum AlternativeAccount) (:$);

cards;

AA 1 A1

AA 1 A2

AA 1 A3

AA 2 A4

AA 2 A3

BB 1 B1

BB 1 B2

CC 1

;

proc sort data=have;

by MainAccount ClientNum AlternativeAccount;run;

data want;

  array aa(100) $2. _temporary_;

  do _n_=1 by 1 until (last.MainAccount);

  set have;

by MainAccount;

  if AlternativeAccount not in aa then aa(_n_)=AlternativeAccount;

  end;

  num_client=0;

  num_account=100-cmiss(of aa(*));

  do until (last.MainAccount);

  set have;

by MainAccount ClientNum AlternativeAccount;

if not missing(AlternativeAccount) then num_client+first.AlternativeAccount;

if last.ClientNum then do; output;num_client=0;end;

  end;

call missing(of aa(*));

drop AlternativeAccount;

run;

proc print;run;

Haikuo

Ksharp
Super User

Yeah. Tom 's code is absolutely right and fast. I just want to rewrite it by using sub-query which is also a powerful tool of SQL as its cartesian product . Don't be offended , TOM.

data have ;

  input (a b c) ($) @@;

cards;
AA 1 A1 AA 1 A2 AA 1 A3 AA 2 A4 AA 2 A3 BB 1 B1 BB 1 B2 CC 1 .
;

proc sql ;
create table want as
select distinct a,b,count(distinct c) as cnt1,(select count(distinct c) from have where a=x.a ) as cnt2
  from have  as x
   group by a,b
;
quit;

Ksharp

Maheshvaran
Calcite | Level 5

Hi all,

Thanks ... this helps a lot.

Regards,

Mahesh

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 4 replies
  • 46579 views
  • 4 likes
  • 4 in conversation