Multiple columns and loop and create ranks

[Emma8]

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.

[mklangley]

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

[Emma8]

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

[mklangley · Accepted solution]

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.