BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
CynthiaWei
Obsidian | Level 7

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:

NameFriend No.
Tom1
Tom2
Tom3
Tom5
Tom6
Tom8
Tom9
Tom10
Mike2
Mike3
Mike4
Mike5
Mike6
Megan1
Megan3
Megan9
Lily1
Lily2
Lily6
Lily7
Lily9
Lily13

 

What I want is as follows:

NameFriend No.Actual counts of friend
Tom18
Tom28
Tom38
Tom58
Tom68
Tom88
Tom98
Tom108
Mike25
Mike35
Mike45
Mike55
Mike65
Megan13
Megan33
Megan93
Lily16
Lily26
Lily66
Lily76
Lily96
Lily136

 

Thank you in advance for any help with this question!

 

Best,

1 ACCEPTED SOLUTION

Accepted Solutions
MichaelLarsen
SAS Employee

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;

View solution in original post

8 REPLIES 8
MichaelLarsen
SAS Employee

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
Obsidian | Level 7

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Obsidian | Level 7
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
Diamond | Level 26 RW9
Diamond | Level 26

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
Obsidian | Level 7
Thank you very much for the response and link!

Best,
s_lassen
Meteorite | Level 14

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
Obsidian | Level 7

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,

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 8 replies
  • 1364 views
  • 0 likes
  • 4 in conversation