How to count actual counts in a sequence by ID

[CynthiaWei]

Hi SAS Pros,

I would like to create an extra column indicating the actual counts of friend that each person has by name, for example: Tom has 8 friends instead of 10 friends because No.4 and 7 didn't show in the column---Friend No.

What I have:

Name	Friend No.
Tom	1
Tom	2
Tom	3
Tom	5
Tom	6
Tom	8
Tom	9
Tom	10
Mike	2
Mike	3
Mike	4
Mike	5
Mike	6
Megan	1
Megan	3
Megan	9
Lily	1
Lily	2
Lily	6
Lily	7
Lily	9
Lily	13

What I want is as follows:

Name	Friend No.	Actual counts of friend
Tom	1	8
Tom	2	8
Tom	3	8
Tom	5	8
Tom	6	8
Tom	8	8
Tom	9	8
Tom	10	8
Mike	2	5
Mike	3	5
Mike	4	5
Mike	5	5
Mike	6	5
Megan	1	3
Megan	3	3
Megan	9	3
Lily	1	6
Lily	2	6
Lily	6	6
Lily	7	6
Lily	9	6
Lily	13	6

Thank you in advance for any help with this question!

Best,

[MichaelLarsen · Accepted solution]

proc sql will do what you want.

data have;
  length Name $8 Friend_No 8;
  input Name Friend_No;
cards;
Tom	1
Tom	2
Tom	3
Tom	5
Tom	6
Tom	8
Tom	9
Tom	10
Mike	2
Mike	3
Mike	4
Mike	5
Mike	6
Megan	1
Megan	3
Megan	9
Lily	1
Lily	2
Lily	6
Lily	7
Lily	9
Lily	13
;
run;
proc sql noprint;
  create table want as
    select
      Name,
      Friend_No,
      count(*) as Friend_Count
    from have
    group by Name 
    order by Name;
quit;

[CynthiaWei]

Thank you so much for solving my problem.

But, there are only three column left, Name, Friend_No, and Friend-Count. In my actual data set, there are more column that I need them to remain in my new data set. And, I want to keep all the variables (columns) in my actual data set. Then, how should I modify the code.

Again, thanks a lot!

Best,

[RW9]

Something like:

proc sql;
  create table want as
  select a.*,
         b.actual
  from   have a 
  left join (select name,count(*) as actual from have group by name) b
  on     a.name=b.name;
quit;

[CynthiaWei]

Thank you so much for replying me question.

What is the a and b in select syntax. Are they random letters?

Could you please give me a actual code based on my data set provided previously?

Thanks a lot!
Best,

[RW9]

A and B are what is known in SQL as Aliases, they are references to the from statement for that particular input (which could be a dataset as in A, or a subquery as in B).

No I cannot provide code to your dataset as you have not provided it in a usable format (it should however be pretty near).  Please refer to how to post test data as this is very important to get full answers:

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

[CynthiaWei]

Thank you very much for the response and link!

Best,

[s_lassen]

You have had a couple of SQL suggestions already. Here is one more, using count(distinct…) just in case there are duplicates on Friend_no:

proc sql;
  create table want as select *,count(distinct Friend_no) as Friend_count
  from have
  group by name;
quit;

[CynthiaWei]

Thank you very much for reminding me possible duplicates. I used your syntax to run. And, it turned to what I want.

Appreciate it!

Best,