I have the following data:
Name Day1 Day2 Day3 Day4
Kim 2 3 9 0
Anna 0 1 2 9
Joe 5 5 9 0
Bob 2 0 9 0
Enward 2 6 5 0
I would like to get the top 3 ranks for each day and summation of remaining data and formatted as a long data as below:
Name Day Counts
Joe Day1 5
Bob Day1 2
Edward Day1 2 (when tie, the take the first name--Edward is before Kim)
Others Day1 2
Edward Day2 6
Joe Day2 5
Kim Day2 3
Others Day2 1
Bob Day3 9
Joe Day3 9
Kim Day3 9
Others Day3 7
Anna Day4 9
Bob Day4 0
Joe Day4 0
Others Day4 0
Actual data have many columns. Thank you.
It shouldn't be repeating day&i., but you may be seeing the day column's format only showing four characters (i.e. so "Day10" is displayed as "Day1").
Try adding the following format statement to the top_3 data step, as shown below:
data top_3;
format day $8.;
/* . . . */
Adding that works for me when I update the input data to have 10+ days.
@Emma8 Is this what you're looking for?
data have;
input name $ day1-day4;
datalines;
Kim 2 3 9 0
Anna 0 1 2 9
Joe 5 5 9 0
Bob 2 0 9 0
Edward 2 6 5 0
;
run;
%macro loop(days=);
%do i = 1 %to &days.;
proc sql;
create table summarize as
select Name, "Day&i." as day, sum(day&i.) as total
from have
group by name, day
order by calculated total desc, name
;
quit;
data top_3;
set summarize end=eof;
retain sum 0;
if _n_ gt 3
then do;
sum + total;
if eof then do;
name = 'Others';
count = sum;
output;
end;
end;
else do;
count = total;
output;
end;
drop total sum;
run;
proc append base=want
data=top_3
force;
run;
%end;
%mend;
%loop(days=4);
Note: Per your example, I am assuming your columns are named Day1, Day2, etc. The parameter of the macro call can be modified if you have more columns (e.g. 7, if you have Day1-Day7).
Result:
It shouldn't be repeating day&i., but you may be seeing the day column's format only showing four characters (i.e. so "Day10" is displayed as "Day1").
Try adding the following format statement to the top_3 data step, as shown below:
data top_3;
format day $8.;
/* . . . */
Adding that works for me when I update the input data to have 10+ days.
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.