🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Quartz | Level 8

Multiple columns and loop and create ranks

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Lapis Lazuli | Level 10

Re: Multiple columns and loop and create ranks

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.

3 REPLIES 3
Lapis Lazuli | Level 10

Re: Multiple columns and loop and create ranks

@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:

Quartz | Level 8

Re: Multiple columns and loop and create ranks

HI-the actual data has ay1 to day50, so I changed days=4 to 50, and the day.i repeat after 9th day. Could you fix that, please? Thank you
Lapis Lazuli | Level 10

Re: Multiple columns and loop and create ranks

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.

Discussion stats
• 3 replies
• 654 views
• 3 likes
• 2 in conversation