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,
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;
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;
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,
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;
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:
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;
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,
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.